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(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.: [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] - [e-mail] - 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] - [e-mail] - 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*******@postgresql.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*******@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.
[phone] - [e-mail] - 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*******@postgresql.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*******@postgresql.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

[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 [e-mail] ("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/>

[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] - [e-mail] - 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*******@postgresql.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*******@postgresql.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*******@postgresql.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] - [e-mail] - 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

[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','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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.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