Re: [GENERAL] PL/Perl returning multiple rows

[Joe Conway]

Joe Conway wrote:
Christopher Murtagh wrote:
 That would work if I could get the Pl/Perl function to return an array
or set of results, but this brings me back to the original problem
(unless I'm missing something obvious).


Sorry, I guess I didn't sufficiently understand the issue. I don't 
really use PL/Perl myself, but I would think there was some way to 
return an array. In the docs, I see:

"Conversely, the return command will accept any string that is 
acceptable input format for the function's declared return type. So, the 
PL/Perl programmer can manipulate data values as if they were just text."

So if you declare the PL/Perl function to return text[], and return a 
properly formatted array, e.g. something like
  "{\"blah blah\",\"foo bar\",\"etc etc\"}"
it ought to work.
Just to follow up, this works:

create or replace function foo(text, text, text)
returns text[] as '
 return "{\\"" . $_[0] . "\\",\\"" . $_[1] . "\\",\\"" . $_[2] . "\\"}";
' language plperl;
regression=# select f[2] from (select foo('blah1','blah2','blah3') as f) 
as t;                                                  f
-------
 blah2
(1 row)

So maybe you can do the syscall and return an array from plperl, then do 
the rest of the work in plpgsql?

Working with arrays in plpgsql in 7.3 is no fun though :(. Here is an 
example that's been posted before:
-------------------------------------------------
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;
    SELECT INTO high
      replace(split_part(array_dims(grolist),'':'',2),'']'','''')::int
      FROM pg_group WHERE grosysid = rec.grosysid;

    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' STABLE STRICT;
------------------------------------------------
grolist is an array. the "SELECT INTO low..." and "SELECT INTO high..." 
parts get you the array index bounds, and the FOR LOOP shows how to work 
with the array elements (i.e. g.grolist[i]).

Hopefully this gets you closer.

Joe

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly