Thread: 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
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