From: Guy Fraser <guy@incentre.net>
To: pgsql-sql@postgresql.org
Subject: Re: Getting multiple rows in plpgsql function
Date: Fri, 24 Jan 2003 18:42:39 -0700
Message-ID: <3E31EB8F.6040606@incentre.net> (raw)
References: <42509.216.86.192.34.1043437147.squirrel@www.la-rubber.com>
<20030124223626.GB24536@cc.usu.edu>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: guy@incentre.net
Subject: Re: Getting multiple rows in plpgsql function
In-Reply-To: <3E31EB8F.6040606@incentre.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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