Re: plpgsql function Syntax

On Wed, 1 Sep 2004 derrick@grifflink.com wrote:

> Is it possible to get a select statement to work like this with a for loop?
>
> CREATE FUNCTION public.search(int4)
>   RETURNS SETOF search1 AS
> '
> DECLARE
> category alias for $1;
> newrows search1%rowtype;
> rec RECORD;
> SQLCommand varchar;
>
> BEGIN
>     IF (category = 0) THEN
>         SQLCommand := 'SELECT * FROM table';
>     ELSE
>         SQLCommand := 'SELECT idnumber FROM table';
>     END IF;
>
>     FOR rec IN SQLCommand
>         LOOP
>             ...blah...
>             ...blah...
>     END LOOP;
>
> Basically I want to create the SELECT statement dynamically and then use
> that select statement in subsequent querries later in the function.  Will
> this syntax work or should I try to use a VIEW?

You should be able to use FOR rec IN EXECUTE SQLCommand LOOP to execute
the string and loop over the results I think.