Re: plpgsql function Syntax

On Wed, 1 Sep 2004 derrick(at)grifflink(dot)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.