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 $$
BEGIN
RETURN str::uuid[];
EXCEPTION WHEN invalid_text_representation THEN
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
Notes:
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/PJAYVK56198uuid[]
parameter to the function: rextester.com/TJOBS12038