输入和反应的函数检查类型

Question

我有一个函数，可以检查数组中的每个元素是否存在于表的给定列中，并且工作正常。现在我想扩展这个函数，以便它能够处理json/jsonb数组以及SQL数组。在这个MWE (PSQL-11)中，注释行是我想要做的事情的psudo代码。保留注释可以让您运行代码，但它只适用于SQL数组。

CREATE TABLE IF NOT EXISTS animals
(
    animal text COLLATE pg_catalog."default" NOT NULL,
    animal_doc jsonb,
    CONSTRAINT animals_pkey PRIMARY KEY (animal)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE animals
    OWNER to postgres;

CREATE OR REPLACE FUNCTION check_is_animal(anyelement) RETURNS boolean AS $
DECLARE
   i text;
   arr text[];
BEGIN
--    IF ((SELECT pg_typeof($1)) = 'jsonb') THEN
--        arr = jsonb_array_elements($1);
--    ELIF ((SELECT pg_typeof($1)) = 'json') THEN
--        arr = json_array_elements($1);
--    ELSE
        arr = $1;
--    END IF;
    FOREACH i IN ARRAY arr LOOP
        IF i NOT IN (SELECT animal FROM animals) THEN
            RAISE NOTICE '% is not a known animal', i;
            RETURN FALSE;
        END IF;
    END LOOP;
    RETURN TRUE;
END;
$ LANGUAGE plpgsql;

INSERT INTO animals (animal) VALUES ('Cat') ON CONFLICT ON CONSTRAINT animals_pkey DO NOTHING;
INSERT INTO animals (animal) VALUES ('Dog') ON CONFLICT ON CONSTRAINT animals_pkey DO NOTHING;

DO $
BEGIN

IF check_is_animal(array['Cat','Dog', 'Horse']) THEN
  RAISE NOTICE 'All good';
ELSE
  RAISE NOTICE 'Failed';
END IF;

/*
IF check_is_animal(jsonb('["Cat","Dog", "Horse"]')) THEN
  RAISE NOTICE 'All good';
ELSE
  RAISE NOTICE 'Failed';
END IF;
*/
END $;

SELECT pg_typeof($1)被发现了，这里。，对于如何使它工作有什么建议吗？

Answer 1

pg_typeof()的结果是一个oid，如果要将它与字符串值进行比较，则需要转换它，例如if pg_typeof($1)::text = 'jsonb' then...

但是，一旦您检查输入是否是JSON，您还需要验证它是否是JSON数组，因为{"foo": "bar"}也将被报告为json(b)。

当您一次又一次地运行相同的select语句时，遍历数组的方式也是相当低效的。您可以用一条语句来检查这一点。

PL/pgSQL中没有ELIF。作为手册中有记载，它需要是ELSIF

所以这个函数应该是这样的：

CREATE OR REPLACE FUNCTION check_is_animal(anyelement) 
  RETURNS boolean 
AS $
DECLARE
   arr text[];
   l_result boolean;
BEGIN
  -- check if the argument is a text array
  -- this could be expanded to check for character varying[] as well
  IF pg_typeof($1)::text = 'text[]' THEN
    arr := $1;
  ELSEIF pg_typeof($1)::text = 'jsonb' THEN
    IF jsonb_typeof($1) = 'array' THEN
      -- check if the array contains strings or objects:
      IF jsonb_typeof($1 -> 0) = 'object' THEN
        -- assuming the key is always name:
        arr := array(select jsonb_array_elements($1) ->> 'name');
      ELSE 
        arr := array(select jsonb_array_elements_text($1::jsonb));
      END IF;
    ELSE 
      return false;
    END IF;
  ELSEIF pg_typeof($1)::text = 'json' THEN
    IF json_typeof($1) = 'array' THEN
      arr := array(select jsonb_array_elements_text($1::jsonb));
    ELSE 
      return false;
    END IF;
  ELSE 
    -- no array whatsoever 
    return false;
  END IF;

  select count(*) = 0
    into l_result
  from unnest(arr) as x(name)
  where not exists (select *
                    from animals a
                    where x.name = a.animal);

  return l_result;
END;
$ LANGUAGE plpgsql;

如果JSON数组包含未知的密钥名，并且不能使用硬编码的name，则可以使用以下方法：

    arr := array(select x.v
                 from jsonb_array_elements($1) as t(e) 
                 cross join jsonb_each_Text(t.e) as x(k,v));

最后的select语句检查数组中的所有元素是否存在于表中。通过使用一个不存在的条件，只要找到一个不存在的动物，该语句就可以停止。

在线示例

Answer 2

在@a_马_使用_不是_名字的帮助下，我将我的原始代码编辑为下面的代码，作为他的解决方案的替代。

CREATE TABLE IF NOT EXISTS animals
(
    animal text COLLATE pg_catalog."default" NOT NULL,
    animal_doc jsonb,
    CONSTRAINT animals_pkey PRIMARY KEY (animal)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE animals
    OWNER to postgres;

CREATE OR REPLACE FUNCTION check_is_animal(anyelement) RETURNS boolean AS $
DECLARE
   i text;
   arr text[];
BEGIN
    IF (pg_typeof($1) = pg_typeof(jsonb('[]'))) THEN
        arr = ARRAY(SELECT jsonb_array_elements_text($1));
    ELSIF (pg_typeof($1) = pg_typeof(json('[]'))) THEN
        arr = ARRAY(SELECT json_array_elements_text($1));
    ELSE
        arr = $1;
    END IF;
    FOREACH i IN ARRAY arr LOOP
        IF i NOT IN (SELECT animal FROM animals) THEN
            RAISE NOTICE '% is not a known animal', i;
            RETURN FALSE;
        END IF;
    END LOOP;
    RETURN TRUE;
END;
$ LANGUAGE plpgsql;

INSERT INTO animals (animal) VALUES ('Cat') ON CONFLICT ON CONSTRAINT animals_pkey DO NOTHING;
INSERT INTO animals (animal) VALUES ('Dog') ON CONFLICT ON CONSTRAINT animals_pkey DO NOTHING;

DO $
BEGIN

IF check_is_animal(array['Cat','Dog', 'Horse']) THEN
  RAISE NOTICE 'All good';
ELSE
  RAISE NOTICE 'Failed';
END IF;

IF check_is_animal(jsonb('["Cat","Dog", "Horse"]')) THEN
  RAISE NOTICE 'All good';
ELSE
  RAISE NOTICE 'Failed';
END IF;

IF check_is_animal(json('["Cat","Dog", "Horse"]')) THEN
  RAISE NOTICE 'All good';
ELSE
  RAISE NOTICE 'Failed';
END IF;

END $;