Re: Where are we on stored procedures?

Neil Conway <neilc(at)samurai(dot)com> writes:
> Tom Lane wrote:
>> Essentially I'm thinking about the JDBC solution, but automated a bit
>> better.

> So would your proposal invent a new "stored procedure" construct, or
> just add some sugar to the existing function stuff? i.e. will you be
> able to issue a CREATE FUNCTION that specifies OUT parameters?

I certainly intend to be able to say OUT in CREATE FUNCTION. I'm not
sure what you consider to be "a new construct". I'm not thinking of
making a new system catalog, for instance, just new column(s) in
pg_proc.

>> This doesn't address the question of SETOF results, of course. I'm
>> leaning towards returning those as cursors.

> This is part of the reason I liked the approach of introduced SQL-level
> variables. Besides being a feature that has some use in itself, it could
> be extended reasonably cleanly to allow (effectively) SETOF variables
> and rowtype variables.

AFAICS that would force every SETOF result to be materialized, which
would be a bit of an efficiency hit. The main reason I don't like it,
though, is that variables in a language that hasn't got control
structures are fundamentally wrong. (And no, I'm not interested in
adding IF and LOOP later ;-)) They could only be useful as an
intermediate step in pushing data from the server to the client, so
why invent all the extra concept and mechanism instead of just pushing
the data immediately?

Basically what I am thinking is that we have all the infrastructure
today to solve the OUT-parameter problem, it's just not wrapped up in
an easy-to-use package.

>> But if you can pass over what you have, I'd like to see about
>> pressing forward.

> Sure, I've attached a very WIP patch with the utility command
> definitions; unfortunately I don't think it will be of much use, as much
> of it is CREATE PROCEDURE-related boilerplate.

Thanks.

> Gavin will update the
> matching-arguments-by-name code to HEAD at some point in the future; I
> believe that works fine for functions (since we just error out in case
> of ambiguity), so we can include it in 8.1 independently on any other
> work on SPs.

Agreed, that should be mostly orthogonal.

regards, tom lane