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
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
