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

[Alvaro Herrera]

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)

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Christopher Browne]

Quoth jd@commandpromp t.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.eviloverlor d.com/>

[Christopher Murtagh]

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.: [phone]
Fax: [phone]

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Joshua D. Drake]

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.
[phone] - jd@commandpromp t.com - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Christopher Murtagh]

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.: [phone]
Fax: [phone]

---------------------------(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

[Joe Conway]

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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

[Joe Conway]

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
---------------------------(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 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.: [phone]
Fax: [phone]

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Joe Conway]

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

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

http://archives.postgresql.org

[Joshua D. Drake]

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.
[phone] - jd@commandpromp t.com - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings