Re: PL/Perl returning multiple rows - Mailing list pgsql-general

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