Postgresql – function checking type of input and reacting

postgresql

I have a function that can check if each element in an array is present in a given column of a table, and it works fine. I would now like to extend the function so that it can handle json/jsonb arrays as well as SQL arrays.
In this MWE (PSQL-11) the commented lines are psudo code for what I would like to do. Keeping them commented out lets you run the code but it then only works for SQL arrays.

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 $$;

The SELECT pg_typeof($1) was found here.
Any suggestions for how the get this to work?

Best Answer

The result of pg_typeof() is an oid, if you want to compare that with a string value, you need to cast it, e.g. if pg_typeof($1)::text = 'jsonb' then...

But once you checked if the input is a JSON you also need to verify if it's a JSON array, because {"foo": "bar"} will also be reported as json(b).

The way you iterate over the array is also quite inefficient as you are running the same select statement over and over again. You can check this with a single statement.

There is no ELIF in PL/pgSQL. As documented in the manual it needs to be ELSIF

So the function would look something like this: 

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;

If the JSON array contains unknown key names and you can't use the hardcoded name, you can use this instead:

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

The final select statement checks if all elements in the array exist in the table. By using a NOT EXISTS condition, the statement can stop as soon as one non-existing animal is found.

Online example

Related Question