From: | Matthew Peter <survivedsushi(at)yahoo(dot)com> |
---|---|
To: | Pandurangan R S <pandurangan(dot)r(dot)s(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: plpgsql question |
Date: | 2006-01-05 08:50:34 |
Message-ID: | 20060105085034.38710.qmail@web35210.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)r(dot)s(at)gmail(dot)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?
---------------------------------
Yahoo! Photos
Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever.
From | Date | Subject | |
---|---|---|---|
Next Message | |N_E_O| | 2006-01-05 11:59:13 | Problem with Starting server 7.4.10_1 on FreeBSD |
Previous Message | Richard Huxton | 2006-01-05 08:47:32 | Re: pg admin III and primary keys (for backup/restore) |