Skip site navigation (1) Skip section navigation (2)

Re: SQL-Invoked Procedures for 8.1

From: Joe Conway <mail(at)joeconway(dot)com>
To: Gavin Sherry <swm(at)linuxworld(dot)com(dot)au>
Cc: Oliver Jowett <oliver(at)opencloud(dot)com>,Grant Finnemore <grantf(at)guruhut(dot)co(dot)za>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: SQL-Invoked Procedures for 8.1
Date: 2004-09-23 15:56:15
Message-ID: 4152F21F.5080502@joeconway.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Gavin Sherry wrote:
> Do you have any idea about databases returning result sets from SQL
> procedures (ie, not functions).
> 

As other's have pointed out, this is very common in the MS SQL Server 
world (and I believe Sysbase also supports it). It works like:

<begin proc def>

select * from something
...
select * from somethingelse
...

<end proc def>

We get requests for this kind of functionality at least a couple of 
times a month, and although it's been a few years since I mucked with 
MSSQL, I found it to be very useful in a number of different circumstances.

It is only workable because stored procedures cannot participate in 
normal SELECT statements. In MSSQL you would do something like:

   exec sp_my_multiresultset_proc
   GO
-- or --
   sp_my_multiresultset_proc
   GO

so the analogy to your stored procedure proposal holds:

   call sp_my_multiresultset_proc();
-- or --
   sp_my_multiresultset_proc();

I had always envisioned implementing this by projecting tuples directly 
the way that SHOW ALL or EXPLAIN ANALYZE do. See, e.g. 
ShowAllGUCConfig() in guc.c.

Joe

In response to

Responses

pgsql-hackers by date

Next:From: Fabien COELHODate: 2004-09-23 16:03:16
Subject: Re: BUG: possible busy loop when connection is closed
Previous:From: Andrew DunstanDate: 2004-09-23 15:41:16
Subject: Re: Use of zlib

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group