PL/Perl returning multiple rows

[Christopher Murtagh]

Greetings,

I'm trying to write a pl/perl function that will return multiple rows.
I've looked all over the web and only found vague references as to how
to do this (some said it was possible, and some said it wasn't but it
was for older versions of Postgres).

Basically I would *love* to be able to do something like this:

SELECT some_id
FROM some_table
WHERE ....
INTERSECT
SELECT perlfunc(somepa rameter);

Is this possible, or do I need to insert results into a temporary table
and intersect with that?

SELECT some_id
FROM some_table
WHERE ....
INTERSECT
SELECT some_id
FROM temp_result
WHERE temp_id = perlfunc(somepa rameter);

Any pointers would be much appreciated.

Thanks in advance.

Cheers,

Chris

--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Communications Group
McGill University
Montreal, Quebec
Canada

Tel.: [phone]
Fax: [phone]

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[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','bl ah2','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_p art(array_dims( grolist),'':'', 1),''['','''')::int
FROM pg_group WHERE grosysid = rec.grosysid;
SELECT INTO high
replace(split_p art(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 ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Christopher Murtagh]

On Tue, 2003-11-11 at 02:01, Joe Conway wrote:

Hopefully this gets you closer.

It definitely does, and it makes it possible to try this in Pl/Perl
combined with Pl/pgSQL. If I can't manage to do it, then I do have C as
an option. Thank you so much for your time, I really appreciate it.
Thanks to everyone else who replied as well.

Cheers,

Chris

--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Communications Group
McGill University
Montreal, Quebec
Canada

Tel.: [phone]
Fax: [phone]

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Alvaro Herrera]

On Mon, Nov 10, 2003 at 11:01:45PM -0800, Joe Conway wrote:

create or replace function foo(text, text, text)
returns text[] as '
return "{\\"" . $_[0] . "\\",\\"" . $_[1] . "\\",\\"" . $_[2] . "\\"}";
' language plperl;

FWIW, this could be written more easily as

create or replace function foo(text, text, text)
returns text[] as '
return qq/{"/ . (join qq/","/, @_) . qq/"}/;
' language plperl;

Somewhat more readable.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Ellos andaban todos desnudos como su madre los parió, y también las mujeres,
aunque no vi más que una, harto moza, y todos los que yo vi eran todos
mancebos, que ninguno vi de edad de más de XXX años" (Cristóbal Colón)

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match