Re: plpgsql question

On 1/5/06, Matthew Peter wrote:
> I'm trying to do a simple SELECT * in plpgsql that returns a set of records
> as a row w/ columns, not a row into a variable, w/ some conditionals.
>
> The function below is semi-pseudo with what I'm trying to... If anyone
> could give me an example that works by returning it as a resultset
> maintaining the columns, that would be awesome and I could take it from
> there.
>
> I've read the pl/pgsql section of the docs and the Douglas book but I'm
> still confused on this issue...
>
> Thanks
>
> create or replace function getrecord(int,text) RETURNS SETOF records as $$
> DECLARE
> -- event := rows to return from the table below
>
> BEGIN
> event := SELECT * FROM my_tbl
> WHERE 1 = 1
> and my_tbl_id IN (0$1) ||
> ' IF $2 IS NOT NULL THEN' || and username = $2 || 'END IF;'
> ; -- end sql statement
>
> RETURN event;
>
> END;
> $$ LANGUAGE plpgsql;
>

Pandurangan R S <pandurangan.r.s@gmail.com> wrote:

Assuming records is the name of a table...

create or replace function getrecord(int,text) RETURNS SETOF records as $$
DECLARE
row records%rowtype;
BEGIN
FOR row IN SELECT * FROM my_tbl
WHERE ...
LOOP
RETURN NEXT row;
END LOOP;

RETURN;

END;
$$ LANGUAGE plpgsql;

Thanks for the reply.

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?

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?