Re: plpgsql dynamic queries and optional arguments

On Wed, Aug 16, 2006 at 02:36:44PM -0500, Curtis Scheer wrote:
> Thanks for the reply I guess what I am actually looking for is an example of
> a dynamic SQL select statement similar to how a static sql select can select
> into a variable.

In 8.1 you can select a single row or columns of a single row with
INTO:

EXECUTE 'SELECT * FROM foo' INTO rec;

Earlier versions don't support INTO with EXECUTE but you can use a
loop to achieve the same effect:

FOR rec IN EXECUTE 'SELECT * FROM foo' LOOP
-- do stuff with rec
END LOOP;

Here are links to the relevant documentation:

http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
http://www.postgresql.org/docs/8.0/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

--
Michael Fuhr