From oogly@rogers.com Sun Feb 2 08:42:00 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 69B3B479790 for ; Sun, 2 Feb 2003 03:42:00 -0500 (EST) Received: from news.hub.org (news.hub.org [64.49.215.80]) by postgresql.org (Postfix) with ESMTP id 77923476036 for ; Sun, 2 Feb 2003 02:21:01 -0500 (EST) Received: by news.hub.org (Postfix, from userid 8) id 6026F43A945; Sun, 2 Feb 2003 02:21:01 -0500 (EST) From: oogly@rogers.com (John C) X-Newsgroups: comp.databases.postgresql.sql Subject: Returning records from a function Date: 1 Feb 2003 23:20:55 -0800 Organization: http://groups.google.com/ Lines: 32 Message-ID: Content-Type: text/plain; charset=ISO-8859-1 X-Complaints-To: groups-abuse@google.com To: pgsql-sql@postgresql.org Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/119 X-Sequence-Number: 11517 I've spent the last few hours trying to come up with a function that at the most basic returns the result of a SELECT * FROM . This is easy to do in something like MSSQL or Oracle. For example in MSSQL i can do something like: CREATE PROCEDURE proc_name AS BEGIN SELECT * FROM sometable END How do I do something like that with a Postgresql function/stored procedure? All I really want to know is how to by using a function how can I return the results of a SELECT * query!? The end result of the query I need is more complicated, but I can't even get something that easy to work. Are functions unable to return result sets?? All of the examples i've seen always return a single value, like an int. That doesn't do me any good. I want to use a few stored procedures to create some faster processing for some queries that involve 3-4 tables plus require some input parameters, and return a result set. Any help would be appriciated. I've just recently started using PostgreSQL and I'm very impressed! I'm sure I'm jsut overlooking something easy, but for the life of me i just cant figure it out. Thanks, John From LBerezhny@DevIS.com Thu Feb 6 19:01:23 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 838E9475AA1 for ; Thu, 6 Feb 2003 14:01:23 -0500 (EST) Received: from netbdc01.devis.com (unknown [209.219.127.12]) by postgresql.org (Postfix) with ESMTP id E185D475A80 for ; Thu, 6 Feb 2003 14:01:22 -0500 (EST) Received: from [10.10.10.105] (10.10.10.12 [10.10.10.12]) by netbdc01.devis.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id 1LXVPMX6; Thu, 6 Feb 2003 13:31:20 -0500 Subject: Re: Returning records from a function From: Lex Berezhny To: John C Cc: pgsql-sql@postgresql.org In-Reply-To: References: Content-Type: text/plain Organization: Message-Id: <1044557855.3555.4.camel@hortus> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.1 Date: 06 Feb 2003 13:57:35 -0500 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/128 X-Sequence-Number: 11526 Hi John, It is in the documentation ;-) http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html Also, this tutorial might be helpful: http://techdocs.postgresql.org/guides/SetReturningFunctions good luck, - lex On Sun, 2003-02-02 at 02:20, John C wrote: > I've spent the last few hours trying to come up with a function that > at the most basic returns the result of a SELECT * FROM
. > > This is easy to do in something like MSSQL or Oracle. For example in > MSSQL i can do something like: > > CREATE PROCEDURE proc_name AS > > BEGIN > SELECT * FROM sometable > END > > > How do I do something like that with a Postgresql function/stored > procedure? All I really want to know is how to by using a function how > can I return the results of a SELECT * query!? > > The end result of the query I need is more complicated, but I can't > even get something that easy to work. Are functions unable to return > result sets?? All of the examples i've seen always return a single > value, like an int. That doesn't do me any good. > > I want to use a few stored procedures to create some faster processing > for some queries that involve 3-4 tables plus require some input > parameters, and return a result set. > > Any help would be appriciated. I've just recently started using > PostgreSQL and I'm very impressed! I'm sure I'm jsut overlooking > something easy, but for the life of me i just cant figure it out. > > Thanks, > John > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org