Hi,
Here's my general situation: I have a function which takes in an optional
ARRAY of Ids as so:
RETURN QUERY SELECT * FROM a WHERE a.id = ANY(v_ids) or v_ids is null;
However it seems that the ...or v_ids is null... bit forces a sequential
scan on a. Reading this list, it seems the best way to get over this is to
dynamically prepare a statement, perhaps something like this:
DECLARE
v_base text;
v_where text := '';
v_rec record;
BEGIN
v_base := 'SELECT * FROM a';
IF (v_ids IS NOT NULL) then
v_where := ' WHERE a.id = ANY(v_ids)';
END IF;
FOR v_rec IN EXECUTE v_base || v_where LOOP
RETURN NEXT v_rec;
END LOOP;
I picked up the looping returning trick next elsewhere in this list, and
presume that's the only way to handle dynamically returning a SET OF.
However the problem I'm having is with substituting in the ARRAY parameter
in to the WHERE string. Obviously the above doesn't quite work since the
named parameter v_ids isn't valid in the statement. I probably need some
kind of array_tostring function to write out the array explicitly, but I was
wondering if there was any other way to do this since the excessive
parameter processing could potentially defeat the purpose of using arrays in
the first place!
Thanks
Shak