Hello 8.4 suport USING clause - so there is possible use array variable directly. regards Pavel Stehule 2009/6/11 Shakil Shaikh <sshaikh@xxxxxxxxxxx>: > 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
