Re: SQL-Invoked Procedures for 8.1

On Thu, 23 Sep 2004, Joe Conway wrote:

> 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.

That's fairly bizarre (at least to my view of the world). Say we could
have OUT parameters which were of some SETOF style type I think that would
solve the same problem.

If we wanted to just return the data as if a SELECT had been issued there
might be some tricky issues for clients like psql of the row descriptor
changed (more rows, new types, etc). On the server side, though, it should
be as simple as reinitialising the destination receiver -- although I
haven't looked at it that closely yet.

Gavin