Re: plpgsql question

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