Re: SQL-Invoked Procedures for 8.1

On Sat, 2 Oct 2004, Joe Conway wrote:

> Gavin Sherry wrote:
> > 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.
>
> That won't satify people moving over from MSSQL/Sybase, but then again,
> maybe the community at-large doesn't think it is important to satify
> that group of users.

As Tom said in another email, if we want to support a single query
generating multiple result sets, we're going to have to break a few
things. I'd imagine that if we supported something like a cursor type for
OUT parameters, the ODBC driver could be modified to apparently return
multiple result sets by scrolling through the cursors.

>
> I think this part of the thread actually ties in with the discussion
> regarding beginning/committing transactions within stored procedures.
> Think of a stored procedure as a parameterized sql script that is run
> from within a single statement, rather than as a series of statements
> piped in from a file. In such a file, you might do
>
> begin;
> INSERT ...;
> UPDATE ...;
> commit;
> SELECT ...;
> CREATE TEMP TABLE foo AS SELECT ...
> UPDATE ...;
> SELECT ...;
>
> in order to perform a series of actions while being able to see interim
> results. In MSSQL, a stored procedure can be (and very often is) used to
> do something exactly like the above (perhaps related to loading of a
> data warehouse, or in an interface between two business systems). In
> fact, T-SQL (the MSSQL/Sybase SQL variant) also supports simple
> branching, variable assignment, and conditionals, which makes it
> possible to do some fairly complex processing in stored procs. This is
> the direction I always hoped Postgres would go with stored procedures.

I see the same use for SPs in Postgres. The basic language features,
however, would be supported through the use of PL/PgSQL (and other
languages). Did you have something else in mind?

>
> Joe
>

Gavin