pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting multiple rows in plpgsql function
3+ messages / 3 participants
[nested] [flat]
* Getting multiple rows in plpgsql function
@ 2003-01-24 19:39 David Durst <ddurst@larubber.com>
2003-01-24 22:36 ` Re: Getting multiple rows in plpgsql function Roberto Mello <rmello@cc.usu.edu>
0 siblings, 1 reply; 3+ messages in thread
From: David Durst @ 2003-01-24 19:39 UTC (permalink / raw)
To: pgsql-sql
I am wondering how you would handle a select that returns multiple rows
in a plpgsql function?
In other words lets say I wanted to iterate through the results in
the function.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Getting multiple rows in plpgsql function
2003-01-24 19:39 Getting multiple rows in plpgsql function David Durst <ddurst@larubber.com>
@ 2003-01-24 22:36 ` Roberto Mello <rmello@cc.usu.edu>
2003-01-25 01:42 ` Re: Getting multiple rows in plpgsql function Guy Fraser <guy@incentre.net>
0 siblings, 1 reply; 3+ messages in thread
From: Roberto Mello @ 2003-01-24 22:36 UTC (permalink / raw)
To: David Durst <ddurst@larubber.com>; +Cc: pgsql-sql
On Fri, Jan 24, 2003 at 11:39:07AM -0800, David Durst wrote:
> I am wondering how you would handle a select that returns multiple rows
> in a plpgsql function?
>
> In other words lets say I wanted to iterate through the results in
> the function.
There are examples in the PL/pgSQL documentation that show you how to do it.
-Roberto
--
+----| Roberto Mello - http://www.brasileiro.net/ |------+
+ Computer Science Graduate Student, Utah State University +
+ USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ +
And God said: E = ½mv² - Ze²/r, and there was light.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Getting multiple rows in plpgsql function
2003-01-24 19:39 Getting multiple rows in plpgsql function David Durst <ddurst@larubber.com>
2003-01-24 22:36 ` Re: Getting multiple rows in plpgsql function Roberto Mello <rmello@cc.usu.edu>
@ 2003-01-25 01:42 ` Guy Fraser <guy@incentre.net>
0 siblings, 0 replies; 3+ messages in thread
From: Guy Fraser @ 2003-01-25 01:42 UTC (permalink / raw)
To: pgsql-sql
NOTE: This is a feature in 7.3 it was either added or fixed, so you will not
be able to do this unless you are using version 7.3. Remember to backup with
pg_dumpall before you upgrade.
This is a sample sent to me earlier this week, that iterates an integer array:
########Cut Here########
CREATE TYPE group_view AS (grosysid int4, groname name, usesysid int4, usename
name);
CREATE OR REPLACE FUNCTION expand_groups() RETURNS SETOF group_view AS '
DECLARE
rec record;
groview record;
low int;
high int;
BEGIN
FOR rec IN SELECT grosysid FROM pg_group LOOP
SELECT INTO low
replace(split_part(array_dims(grolist),'':'',1),''['','''')::int
FROM pg_group WHERE grosysid = rec.grosysid;
IF low IS NULL THEN
low := 1;
high := 1;
ELSE
SELECT INTO high
replace(split_part(array_dims(grolist),'':'',2),'']'','''')::int
FROM pg_group WHERE grosysid = rec.grosysid;
IF high IS NULL THEN
high := 1;
END IF;
END IF;
FOR i IN low..high LOOP
SELECT INTO groview g.grosysid, g.groname, s.usesysid, s.usename
FROM pg_shadow s join pg_group g on s.usesysid = g.grolist[i]
WHERE grosysid = rec.grosysid;
RETURN NEXT groview;
END LOOP;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql' WITH ( iscachable, isstrict );
CREATE VIEW groupview AS SELECT * FROM expand_groups();
########Cut Here########
One of the tricks is that you apparently need to use the CREATE TYPE commands
to define the returned result. The veiw at the end just makes queries look
like a table is being queried rather than a function.
I hope this helps.
Roberto Mello wrote:
> On Fri, Jan 24, 2003 at 11:39:07AM -0800, David Durst wrote:
>
>>I am wondering how you would handle a select that returns multiple rows
>>in a plpgsql function?
>>
>>In other words lets say I wanted to iterate through the results in
>>the function.
>
>
> There are examples in the PL/pgSQL documentation that show you how to do it.
>
> -Roberto
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-01-25 01:42 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-24 19:39 Getting multiple rows in plpgsql function David Durst <ddurst@larubber.com>
2003-01-24 22:36 ` Roberto Mello <rmello@cc.usu.edu>
2003-01-25 01:42 ` Guy Fraser <guy@incentre.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox