On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote: > Is it possible to skip the loop and just return all records in a > single query and shove all those rows into a table variable? Not in PL/pgSQL -- you need to return each row with RETURN NEXT, generally from within a loop. Why do you want to avoid that? For simple functions you could use SQL instead of PL/pgSQL: http://www.postgresql.org/docs/8.1/interactive/xfunc-sql.html#AEN31627 > Also, the WHERE part is also important cause I'm not sure i got > that part right? Would this call for EXECUTE or will it be okay and > be planned the first time by the query planner? If each call to the function issues the same query, just with different values, then you shouldn't need to use EXECUTE. If the query differs depending on the function parameters then you have several possibilities: * You could build the query string and use EXECUTE. Be sure to read about quote_literal() and quote_ident(). * You could use an IF statement to execute the query you need. * You could put the queries in separate functions. You can use the same name for different functions if their call signatures are different, e.g., getrecord(integer) and getrecord(integer, text). * You could rewrite the query, possibly using CASE or COALESCE to handle NULL values. -- Michael Fuhr
