How would I pass a list that is either full of UUIDs or empty, then return only joined records with those id values or all records if empty?

This question (Casting an array of texts to an array of UUIDs) asks for the answer I am looking for, but the selected answer does not answer the question[, it answers the asked text and addresses the errors in the OP's situation].

The closest to correctly fulfilling the need is:

FUNCTION getstuff(id_list text[])
  RETURNS TABLE (a_id uuid, b_id, uuid, <other values)
  ... <snip> ... $$
  SELECT a.id, b.id, <etc.>
    FROM schema.a a, schema.b b
    WHERE <other_tests> 
      AND b.id::text = ANY (id_list)
        OR b.id = uuid_or_null(id_list)

CREATE OR REPLACE FUNCTION uuid_or_null(str text)
RETURNS uuid AS $$
  RETURN str::uuid[];
EXCEPTION WHEN invalid_text_representation THEN
$$ LANGUAGE plpgsql;

Also tried has been CARDINALITY(id_list)=0, expecting it to yield TRUE when the id_list array was empty, and thus also return all rows, but it failed due to a syntax error that's since been lost.

*(uuid_or_null modified from this answer)

  • WHERE b.id::text = ANY (id_list) OR cardinality(id_list) = 0 will work. rextester.com/PJAYVK56198
    – user1822
    Commented Apr 25, 2019 at 20:29
  • I apologize, I thought your code was saved in that rextester and modified it by accident. I may not have explained properly the intent of the code; the id values are used in the join. I created an example here: rextester.com/BKIX87785 Note that the function calls expect to return the same as the standard query above them... (I also apologize if the text[] syntax is incorrect.) Commented Apr 25, 2019 at 20:59
  • That's because you are using wrong string literals for the UUID. If you write the string literal in lowercase it works: rextester.com/CJD78556 or better: pass a proper uuid[] parameter to the function: rextester.com/TJOBS12038
    – user1822
    Commented Apr 26, 2019 at 4:35


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.