Thread: PL/Perl returning multiple rows
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
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"
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
>>. >> >>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
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
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"
>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
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 >
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
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 > > > >
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)
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
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
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
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
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
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
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
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
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
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
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/>
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)