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

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

[Christopher Murtagh]

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

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

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

[Joshua D. Drake]

.

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

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

[Joe Conway]

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


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

[Alvaro Herrera]

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

[Joshua D. Drake]

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

[Ben]

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

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

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

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

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

[Ben]

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 ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

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