PL/PGSQL: another Q

On Fri, 17 Jan 2003, Jean-Christian Imbeault wrote:

I've gone through the books I have and searched the web but can't find
an answer to this question.

Is it possible to have a plpgsql function return a result set (rows)? If
so what do I declare the return type as?

I know it sounds silly but the reason would be that I have these
extremely long queries that I hate typing. I'd love to just create a
function, do a "SELECT myfunc(1,2,3,4,5,6,7)" and have the function spew
out the result set (rows). So far I can only find example on how to get
PL/PGSQL to return a single value.

Can this be done in PL/PGSQL?

As of 7.3 it can be done reasonably. You might want to look at the
document on set returning functions that was just put up on techdocs.

This is available is 7.3 version.
See http://developer.postgresql.org/docs/postgres/xfunc-sql.html#AEN31522

Patrick

----------------------------------------------------------------------------
---------------
Patrick Fiche
email : patrick.fiche@aqsacom.com
t�l : 01 69 29 36 18
----------------------------------------------------------------------------
---------------

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jean-Christian
Imbeault
Sent: Friday, January 17, 2003 7:34 AM
To: pgsql-general
Subject: [GENERAL] PL/PGSQL: another Q

I've gone through the books I have and searched the web but can't find
an answer to this question.

Is it possible to have a plpgsql function return a result set (rows)? If
so what do I declare the return type as?

I know it sounds silly but the reason would be that I have these
extremely long queries that I hate typing. I'd love to just create a
function, do a "SELECT myfunc(1,2,3,4,5,6,7)" and have the function spew
out the result set (rows). So far I can only find example on how to get
PL/PGSQL to return a single value.

Can this be done in PL/PGSQL?

Any pointers to reference material greatly appreciated!

Jc

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

http://archives.postgresql.org

Sorry for the previous wrong answer, if was SQL Functions...
Here is the link for PL/pgSQL functions
http://techdocs.postgresql.org/guides/SetReturningFunctions

----------------------------------------------------------------------------
---------------
Patrick Fiche
email : patrick.fiche@aqsacom.com
t�l : 01 69 29 36 18
----------------------------------------------------------------------------
---------------

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jean-Christian
Imbeault
Sent: Friday, January 17, 2003 7:34 AM
To: pgsql-general
Subject: [GENERAL] PL/PGSQL: another Q

I've gone through the books I have and searched the web but can't find
an answer to this question.

Is it possible to have a plpgsql function return a result set (rows)? If
so what do I declare the return type as?

I know it sounds silly but the reason would be that I have these
extremely long queries that I hate typing. I'd love to just create a
function, do a "SELECT myfunc(1,2,3,4,5,6,7)" and have the function spew
out the result set (rows). So far I can only find example on how to get
PL/PGSQL to return a single value.

Can this be done in PL/PGSQL?

Any pointers to reference material greatly appreciated!

Jc

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

http://archives.postgresql.org