From oogly@rogers.com Sun Feb 2 15:44:06 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 567654758DC for ; Sun, 2 Feb 2003 10:44:06 -0500 (EST) Received: from fep03-mail.bloor.is.net.cable.rogers.com (fep03-mail.bloor.is.net.cable.rogers.com [66.185.86.73]) by postgresql.org (Postfix) with ESMTP id 810A4475843 for ; Sun, 2 Feb 2003 10:44:05 -0500 (EST) Received: from spook ([65.48.10.226]) by fep03-mail.bloor.is.net.cable.rogers.com (InterMail vM.5.01.05.06 201-253-122-126-106-20020509) with ESMTP id <20030202154406.KLEG121195.fep03-mail.bloor.is.net.cable.rogers.com@spook> for ; Sun, 2 Feb 2003 10:44:06 -0500 Message-ID: <000d01c2cad1$eb3a7e50$6401a8c0@spook> From: "John Cavacas" To: Subject: How to return records from a function Date: Sun, 2 Feb 2003 10:44:05 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Authentication-Info: Submitted using SMTP AUTH LOGIN at fep03-mail.bloor.is.net.cable.rogers.com from [65.48.10.226] using ID at Sun, 2 Feb 2003 10:44:06 -0500 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/33 X-Sequence-Number: 11431 Hello everyone. 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 appreciated. 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 can't figure it out. Thanks, John From jasiek@serwer.skawsoft.com.pl Sun Feb 2 15:58:50 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 00316475956 for ; Sun, 2 Feb 2003 10:58:50 -0500 (EST) Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66]) by postgresql.org (Postfix) with ESMTP id 66DFC47592C for ; Sun, 2 Feb 2003 10:58:50 -0500 (EST) Received: by serwer.skawsoft.com.pl (Postfix, from userid 1000) id CCE232B889; Sun, 2 Feb 2003 16:52:41 +0100 (CET) Date: Sun, 2 Feb 2003 16:52:41 +0100 To: John Cavacas Cc: pgsql-sql@postgresql.org Subject: Re: How to return records from a function Message-ID: <20030202155241.GA5672@serwer> References: <000d01c2cad1$eb3a7e50$6401a8c0@spook> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <000d01c2cad1$eb3a7e50$6401a8c0@spook> User-Agent: Mutt/1.3.28i From: jasiek@serwer.skawsoft.com.pl X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/120 X-Sequence-Number: 11518 Did you look at http://techdocs.postgresql.org/guides/SetReturningFunctions ? You need Postgresql 7.3 to do this. Regards, Tomasz Myrta From oogly@rogers.com Sun Feb 2 16:34:54 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 8666D475956 for ; Sun, 2 Feb 2003 11:34:54 -0500 (EST) Received: from fep03-mail.bloor.is.net.cable.rogers.com (fep03-mail.bloor.is.net.cable.rogers.com [66.185.86.73]) by postgresql.org (Postfix) with ESMTP id B21A747592C for ; Sun, 2 Feb 2003 11:34:53 -0500 (EST) Received: from spook ([65.48.10.226]) by fep03-mail.bloor.is.net.cable.rogers.com (InterMail vM.5.01.05.06 201-253-122-126-106-20020509) with ESMTP id <20030202163455.MDMY121195.fep03-mail.bloor.is.net.cable.rogers.com@spook> for ; Sun, 2 Feb 2003 11:34:55 -0500 Message-ID: <001a01c2cad9$044a5710$6401a8c0@spook> From: "John Cavacas" To: References: <000d01c2cad1$eb3a7e50$6401a8c0@spook> <20030202155241.GA5672@serwer> Subject: Re: How to return records from a function Date: Sun, 2 Feb 2003 11:34:54 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Authentication-Info: Submitted using SMTP AUTH LOGIN at fep03-mail.bloor.is.net.cable.rogers.com from [65.48.10.226] using ID at Sun, 2 Feb 2003 11:34:55 -0500 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/34 X-Sequence-Number: 11432 Thanks! That's somewhat of what i was looking for. However it seems that what I wanted to do is still not possible. I'm writing a Java application and wanted to use CallableStatement to call a PostgreSQL function. However it seems that the driver does not implement that functionality. I guess I can just use a regular statement calling a function but there seems to be problems doing that as well. Are there any other JDBC drivers for PostgreSQL? Thanks, john ----- Original Message ----- From: To: "John Cavacas" Cc: Sent: February 2, 2003 10:52 AM Subject: Re: [SQL] How to return records from a function > Did you look at > http://techdocs.postgresql.org/guides/SetReturningFunctions > ? You need Postgresql 7.3 to do this. > Regards, > Tomasz Myrta