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 <[EMAIL PROTECTED]> wrote:
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?
> 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 <[EMAIL PROTECTED]> 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?
Yahoo! Photos
Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever.