Thread: PL/Perl returning multiple rows

PL/Perl returning multiple rows

From
Christopher Murtagh
Date:
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(someparameter);

 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(someparameter);

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.: (514) 398-3122
Fax:  (514) 398-2017

Re: PL/Perl returning multiple rows

From
Alvaro Herrera
Date:
On Mon, Nov 10, 2003 at 03:59:53PM -0500, Christopher Murtagh wrote:

>  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).

Unfortunately, pl/perl has not been enabled to return multiple rows yet.
Apparently it also can use SPI only through an experimental interface ...
so plperl is pretty much a dead end for you.

Better start learning Tcl ...

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"The ability to monopolize a planet is insignificant
next to the power of the source"

Re: PL/Perl returning multiple rows

From
Christopher Murtagh
Date:
On Mon, 2003-11-10 at 16:52, Alvaro Herrera wrote:
> On Mon, Nov 10, 2003 at 03:59:53PM -0500, Christopher Murtagh wrote:
>
> >  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).
>
> Unfortunately, pl/perl has not been enabled to return multiple rows yet.
> Apparently it also can use SPI only through an experimental interface ...
> so plperl is pretty much a dead end for you.
>
> Better start learning Tcl ...

 Thanks for the info. I'm ok with that, I like Perl, but I can live
without it too. :-) Two questions:

1) Can Tcl return multiple rows?

2) Do you know where I can find some sample pl/tcl code? I've found the
postgres docs, but there isn't a lot there.

3) ok, 3 questions... Any word on pl/php and a release date?

Thanks again.

Cheers,

Chris

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

Tel.: (514) 398-3122
Fax:  (514) 398-2017

Re: PL/Perl returning multiple rows

From
"Joshua D. Drake"
Date:

>>.
>>
>>Better start learning Tcl ...
>>
>>
>
>
>
What are we torturing people now? Can plPython do this?


> Thanks for the info. I'm ok with that, I like Perl, but I can live
>without it too. :-) Two questions:
>
>1) Can Tcl return multiple rows?
>
>2) Do you know where I can find some sample pl/tcl code? I've found the
>postgres docs, but there isn't a lot there.
>
>3) ok, 3 questions... Any word on pl/php and a release date?
>
>Thanks again.
>
>Cheers,
>
>Chris
>
>
>

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org



Re: PL/Perl returning multiple rows

From
Joe Conway
Date:
Christopher Murtagh wrote:
> On Mon, 2003-11-10 at 16:52, Alvaro Herrera wrote:
>>Better start learning Tcl ...
>
>  Thanks for the info. I'm ok with that, I like Perl, but I can live
> without it too. :-) Two questions:
>
> 1) Can Tcl return multiple rows?
> 3) ok, 3 questions... Any word on pl/php and a release date?


AFAIK, the only PLs that support returning multiple rows at the moment
are SQL, PL/pgSQL, and PL/R.

Joe




Re: PL/Perl returning multiple rows

From
Alvaro Herrera
Date:
On Mon, Nov 10, 2003 at 03:00:34PM -0800, Joshua D. Drake wrote:

> >>Better start learning Tcl ...

> What are we torturing people now? Can plPython do this?

Well, aparently Tcl is not up to the task either, nor is plPython.  At
least I can find no mention on the docs nor the source code.  Can your
plPHP or plPerl do it?

It seems the only choices left are PL/pgSQL and C ...

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Acepta los honores y aplausos y perderás tu libertad"

Re: PL/Perl returning multiple rows

From
"Joshua D. Drake"
Date:
>Well, aparently Tcl is not up to the task either, nor is plPython.  At
>least I can find no mention on the docs nor the source code.  Can your
>plPHP or plPerl do it?
>
>
>
I know the plPHP can't yet. The plPerl probably can't either. On the list
for 1.2 of both of those is native SPI and multiple rows.

J



>It seems the only choices left are PL/pgSQL and C ...
>
>
>

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org



Re: PL/Perl returning multiple rows

From
Ben
Date:
Sorry, I'm coming into this thread late so I must have missed something...
what's wrong with pl/pgsql?

On Mon, 10 Nov 2003, Alvaro Herrera wrote:

> On Mon, Nov 10, 2003 at 03:00:34PM -0800, Joshua D. Drake wrote:
>
> > >>Better start learning Tcl ...
>
> > What are we torturing people now? Can plPython do this?
>
> Well, aparently Tcl is not up to the task either, nor is plPython.  At
> least I can find no mention on the docs nor the source code.  Can your
> plPHP or plPerl do it?
>
> It seems the only choices left are PL/pgSQL and C ...
>
> --
> Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
> "Acepta los honores y aplausos y perderás tu libertad"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>



Re: PL/Perl returning multiple rows

From
"Joshua D. Drake"
Date:

Ben wrote:

>Sorry, I'm coming into this thread late so I must have missed something...
>what's wrong with pl/pgsql?
>
>
The fact that it is pl/pgSQL? Seriously though, I think that pl/pgSQL is
counter
intuitive to some people and those of us who are coming from say a Perl
background are going to be much more proficient in using pl/Perl then having
to learn YET another language.

Sincerely,

Joshua Drake



>On Mon, 10 Nov 2003, Alvaro Herrera wrote:
>
>
>
>>On Mon, Nov 10, 2003 at 03:00:34PM -0800, Joshua D. Drake wrote:
>>
>>
>>
>>>>>Better start learning Tcl ...
>>>>>
>>>>>
>>>What are we torturing people now? Can plPython do this?
>>>
>>>
>>Well, aparently Tcl is not up to the task either, nor is plPython.  At
>>least I can find no mention on the docs nor the source code.  Can your
>>plPHP or plPerl do it?
>>
>>It seems the only choices left are PL/pgSQL and C ...
>>
>>--
>>Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
>>"Acepta los honores y aplausos y perderás tu libertad"
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 8: explain analyze is your friend
>>
>>
>>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>
>

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org



Re: PL/Perl returning multiple rows

From
Ben
Date:
Oh, well yeah, of course there's always that. :)

But once the original poster said "better start learning Tcl" the
commitment had already been made to learn something new. So why not just
go straight for pl/pgsql? Granted, it's got a learning curve, but I find
it a lot easier to tinker with than C functions when you don't know what
you're doing.

On Mon, 2003-11-10 at 18:11, Joshua D. Drake wrote:
> Ben wrote:
>
> >Sorry, I'm coming into this thread late so I must have missed something...
> >what's wrong with pl/pgsql?
> >
> >
> The fact that it is pl/pgSQL? Seriously though, I think that pl/pgSQL is
> counter
> intuitive to some people and those of us who are coming from say a Perl
> background are going to be much more proficient in using pl/Perl then having
> to learn YET another language.
>
> Sincerely,
>
> Joshua Drake
>
>
>
> >On Mon, 10 Nov 2003, Alvaro Herrera wrote:
> >
> >
> >
> >>On Mon, Nov 10, 2003 at 03:00:34PM -0800, Joshua D. Drake wrote:
> >>
> >>
> >>
> >>>>>Better start learning Tcl ...
> >>>>>
> >>>>>
> >>>What are we torturing people now? Can plPython do this?
> >>>
> >>>
> >>Well, aparently Tcl is not up to the task either, nor is plPython.  At
> >>least I can find no mention on the docs nor the source code.  Can your
> >>plPHP or plPerl do it?
> >>
> >>It seems the only choices left are PL/pgSQL and C ...
> >>
> >>--
> >>Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
> >>"Acepta los honores y aplausos y perderás tu libertad"
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 8: explain analyze is your friend
> >>
> >>
> >>
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 3: if posting/reading through Usenet, please send an appropriate
> >      subscribe-nomail command to majordomo@postgresql.org so that your
> >      message can get through to the mailing list cleanly
> >
> >


Re: PL/Perl returning multiple rows

From
Alvaro Herrera
Date:
On Mon, Nov 10, 2003 at 06:27:24PM -0800, Ben wrote:
> Oh, well yeah, of course there's always that. :)
>
> But once the original poster said "better start learning Tcl" the
> commitment had already been made to learn something new. So why not just
> go straight for pl/pgsql? Granted, it's got a learning curve, but I find
> it a lot easier to tinker with than C functions when you don't know what
> you're doing.

Well, I wasn't the OP ;-).  I thought Tcl had the capability, as it is
sometimes said to be the most advanced PL.  Plpgsql _is_ clumsy to
work on ... If I had to learn something new I'm not sure I'd choose
plpgsql ...

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La persona que no quería pecar / estaba obligada a sentarse
en duras y empinadas sillas    / desprovistas, por cierto
de blandos atenuantes"                          (Patricio Vogel)

Re: PL/Perl returning multiple rows

From
Christopher Murtagh
Date:
On Mon, 2003-11-10 at 21:11, Joshua D. Drake wrote:
> The fact that it is pl/pgSQL? Seriously though, I think that pl/pgSQL is
> counter intuitive to some people and those of us who are coming from say a Perl
> background are going to be much more proficient in using pl/Perl then having
> to learn YET another language.

 Thanks for all the feedback. I've written a bunch of triggers in
pl/pgsql and it wasn't the worst thing. The reason why I was thinking
pl/perl is because my perl function needs to make a system call (to
htDig actually) and extract integers from URLs that htDig give it.

 I know I could write this in Perl with my eyes closed, but I'm not so
sure how I would do this with pgSQL. Can you even make pl/pgSQL
'untrusted' to make syscalls?

 Basically, what I'm doing is using htDig to index and search text
objects within Postgres. I spent a lot of time trying to get GiST and
tsearch to work, but the lack of documentation and complexity of it made
it impossible. Plus, htDig already has features that allow it to ignore
HTML, phrase searching as well as fuzzy logic for lexemes, soudex and
whatnot. We donated a G4 (and hopefully more soon) to the htDig team to
help get 3.2 out of beta, and it is paying off big time. Here's a
prototype of one component of the search engine:

 http://newfind.mcgill.ca/ads/

which basically is an index of:

 http://www.mcgill.ca/classified/

 That search tool works well, but it is a PHP wrapper/hack. I would much
rather do it at the DB level rather than PHP as it makes it a much more
powerful tool. If I had a month or two, I would take the htDig source
and make it a Postgres plugin, but unfortunately I don't.

 The worst part of this is that I have about two days to finish building
this. :-(

 So, perhaps I should stick with Perl for now, and hope that with a real
SPI, the speed will improve significantly. Someone mentioned earlier
that there is an experimental SPI... just how experimental exactly?
Segfault and die or less dangerous?

 Thanks again for all the feedback. I'd be happy for any more thoughts
and ideas.

Cheers,

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

Tel.: (514) 398-3122
Fax:  (514) 398-2017

Re: PL/Perl returning multiple rows

From
"Joshua D. Drake"
Date:
Hello,

   If you can code in Perl then pl/C wouldn't be a deep jump.

J

Christopher Murtagh wrote:

>On Mon, 2003-11-10 at 21:11, Joshua D. Drake wrote:
>
>
>>The fact that it is pl/pgSQL? Seriously though, I think that pl/pgSQL is
>>counter intuitive to some people and those of us who are coming from say a Perl
>>background are going to be much more proficient in using pl/Perl then having
>>to learn YET another language.
>>
>>
>
> Thanks for all the feedback. I've written a bunch of triggers in
>pl/pgsql and it wasn't the worst thing. The reason why I was thinking
>pl/perl is because my perl function needs to make a system call (to
>htDig actually) and extract integers from URLs that htDig give it.
>
> I know I could write this in Perl with my eyes closed, but I'm not so
>sure how I would do this with pgSQL. Can you even make pl/pgSQL
>'untrusted' to make syscalls?
>
> Basically, what I'm doing is using htDig to index and search text
>objects within Postgres. I spent a lot of time trying to get GiST and
>tsearch to work, but the lack of documentation and complexity of it made
>it impossible. Plus, htDig already has features that allow it to ignore
>HTML, phrase searching as well as fuzzy logic for lexemes, soudex and
>whatnot. We donated a G4 (and hopefully more soon) to the htDig team to
>help get 3.2 out of beta, and it is paying off big time. Here's a
>prototype of one component of the search engine:
>
> http://newfind.mcgill.ca/ads/
>
>which basically is an index of:
>
> http://www.mcgill.ca/classified/
>
> That search tool works well, but it is a PHP wrapper/hack. I would much
>rather do it at the DB level rather than PHP as it makes it a much more
>powerful tool. If I had a month or two, I would take the htDig source
>and make it a Postgres plugin, but unfortunately I don't.
>
> The worst part of this is that I have about two days to finish building
>this. :-(
>
> So, perhaps I should stick with Perl for now, and hope that with a real
>SPI, the speed will improve significantly. Someone mentioned earlier
>that there is an experimental SPI... just how experimental exactly?
>Segfault and die or less dangerous?
>
> Thanks again for all the feedback. I'd be happy for any more thoughts
>and ideas.
>
>Cheers,
>
>Chris
>
>


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org



Re: PL/Perl returning multiple rows

From
Christopher Murtagh
Date:
On Mon, 2003-11-10 at 23:05, Joshua D. Drake wrote:
> Hello,
>
>    If you can code in Perl then pl/C wouldn't be a deep jump.

 That might not be a bad idea. Haven't done much C programming since my
CS days, but I really loved it then.

 Other than here:

 http://www.postgres.org/docs/7.3/static/xfunc-c.html

 Can you recommend more reading on writing C functions for Postgres?
Books, anything?

 Thanks again.

Cheers,

Chris


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

Tel.: (514) 398-3122
Fax:  (514) 398-2017

Re: PL/Perl returning multiple rows

From
Joe Conway
Date:
Christopher Murtagh wrote:
> On Mon, 2003-11-10 at 21:11, Joshua D. Drake wrote:
>
>>The fact that it is pl/pgSQL? Seriously though, I think that pl/pgSQL is
>>counter intuitive to some people and those of us who are coming from say a Perl
>>background are going to be much more proficient in using pl/Perl then having
>>to learn YET another language.
>
>  Thanks for all the feedback. I've written a bunch of triggers in
> pl/pgsql and it wasn't the worst thing. The reason why I was thinking
> pl/perl is because my perl function needs to make a system call (to
> htDig actually) and extract integers from URLs that htDig give it.
>
>  I know I could write this in Perl with my eyes closed, but I'm not so
> sure how I would do this with pgSQL. Can you even make pl/pgSQL
> 'untrusted' to make syscalls?

Write a Pl/Perl function that just does the syscall, and call it from
PL/pgSQL. Similarly for complex string parsing, etc.

HTH,

Joe


Re: PL/Perl returning multiple rows

From
Joe Conway
Date:
Alvaro Herrera wrote:
> Well, I wasn't the OP ;-).  I thought Tcl had the capability, as it is
> sometimes said to be the most advanced PL.

Nah, that would be PL/R ;-)

Joe


Re: PL/Perl returning multiple rows

From
Christopher Murtagh
Date:
On Tue, 2003-11-11 at 00:07, Joe Conway wrote:
> Write a Pl/Perl function that just does the syscall, and call it from
> PL/pgSQL. Similarly for complex string parsing, etc.

 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).

Cheers,

Chris

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

Tel.: (514) 398-3122
Fax:  (514) 398-2017

Re: PL/Perl returning multiple rows

From
"Joshua D. Drake"
Date:
Christopher Murtagh wrote:

>On Mon, 2003-11-10 at 23:05, Joshua D. Drake wrote:
>
>
>>Hello,
>>
>>   If you can code in Perl then pl/C wouldn't be a deep jump.
>>
>>
>
> That might not be a bad idea. Haven't done much C programming since my
>CS days, but I really loved it then.
>
> Other than here:
>
> http://www.postgres.org/docs/7.3/static/xfunc-c.html
>
> Can you recommend more reading on writing C functions for Postgres?
>Books, anything?
>
>
PostgreSQL Developer's Handbook.

Also look into ecpg... it will make your life easier.


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org



Re: PL/Perl returning multiple rows

From
Joe Conway
Date:
Christopher Murtagh wrote:
> On Tue, 2003-11-11 at 00:07, Joe Conway wrote:
>>Write a Pl/Perl function that just does the syscall, and call it from
>>PL/pgSQL. Similarly for complex string parsing, etc.
>
>  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.

Joe



Re: PL/Perl returning multiple rows

From
Joe Conway
Date:
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


Re: PL/Perl returning multiple rows

From
Christopher Murtagh
Date:
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.: (514) 398-3122
Fax:  (514) 398-2017

Re: PL/Perl returning multiple rows

From
Christopher Browne
Date:
Quoth jd@commandprompt.com ("Joshua D. Drake"):
> Ben wrote:
>
>>Sorry, I'm coming into this thread late so I must have missed
>>something...  what's wrong with pl/pgsql?
>>
> The fact that it is pl/pgSQL? Seriously though, I think that
> pl/pgSQL is counter intuitive to some people and those of us who are
> coming from say a Perl background are going to be much more
> proficient in using pl/Perl then having to learn YET another
> language.

Perl is certainly more suited to doing wild things with strings
(although I was thinking just yesterday about the notion of PL/Icon
:-), until I recalled that it needs a compilation process that would
make things somewhat inconvenient).

On the other hand, the need to jump through DBD:PgSPI library hoops to
get at functions to do "database stuff" has put off my interest in
doing DB gymnastics in Perl, at least for now.

Different strokes for different languages; not that processing is
_impossible_ on either side, but the things that have 'native
syntactic sugar' in the one language require function calls and extra
work variables in the other.
--
(reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
http://cbbrowne.com/info/x.html
Rules of the Evil Overlord #103. "I  will make it clear that I do know
the meaning of  the word "mercy"; I simply choose  not show them any."
<http://www.eviloverlord.com/>

Re: PL/Perl returning multiple rows

From
Alvaro Herrera
Date:
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)