Proposal: OUT parameters for plpgsql

Awhile back I wrote:
> 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.

Here is a more fully fleshed-out proposal. The basic goal that I'm
aiming at is to be able to use OUT-parameter notation in plpgsql as an
easy way of returning multiple values from a function. There is a lot
of stuff on the table that this doesn't touch, such as procedures
executing outside transaction control, parameter matching by name
instead of position, default values for parameters, etc. I don't want
to do anything that will interfere with adding features like those
later, and so this proposal is just minimal decoration on the existing
ability to create functions that return RECORD.

The generic thing: you can write OUT and INOUT parameters in CREATE
FUNCTION. If you do, you can omit the RETURNS clause, in which case the
result type of the function is implicitly RECORD; or you can specify
RECORD explicitly; or you can specify SETOF RECORD; or, if there's just
one OUT/INOUT param, you can specify that param's type or SETOF it.
(When there is just one, should the default be to return that type
rather than a one-column RECORD?)

Note that the result type is RECORD; we won't explicitly create a named
composite type for such functions. (We could, perhaps, but I think it'd
clutter the catalogs more than be useful.) It might be interesting
however to allow explicit specification of RETURNS existing-composite-type
with a matching set of OUT parameters.

Calling such a function from SQL: you write just the values for the IN and
INOUT parameters, and the result is a record of the OUT and INOUT parameters.
So typical call style would be
SELECT * FROM foo(1,2,'xyzzy');
Unlike with an ordinary RECORD-returning function, you do not specify
an AS list, since the result column names and types are already known.
(We'll have to invent a column name in the case of an OUT parameter that
wasn't given a name in CREATE FUNCTION, but this seems like no big deal.)

Within plpgsql, OUT parameters are variables initialized to NULL, INOUT
parameters are variables initialized with the passed-in values. You write
just RETURN with no argument (or RETURN NEXT in the SETOF case) to pass back
the current values of these variables.

pg_proc changes: add two new columns to pg_proc, paralleling proargnames;
call them something like proargmodes and proallargtypes. proargmodes is an
array of char values that indicate the IN, OUT, or INOUT mode; proallargtypes
is an array of parameter datatype OIDs. In a function declared without any
OUT or INOUT parameters, both of these are set to NULL and the behavior is the
same as before. When there are any OUT or INOUT parameters, the existing
proargtypes array includes only the IN and INOUT parameters (so that it can
continue to have the existing semantics). proallargtypes includes all the
parameter types, and proargmodes shows what's what. proargnames matches up
with proallargtypes, not proargtypes, in this case.

Note the implication that only the IN (and INOUT) parameters determine
questions like whether a function conflicts with another function ---
that is, pg_proc's primary key is still proargtypes not proallargtypes.
Thus for example you couldn't create both
foo(a IN int, x OUT float)
foo(a IN int, y OUT float, z OUT text)
since these would have conflicting input-arguments signatures.

It might be better to go ahead and fill the new pg_proc columns all the
time, even for all-IN-parameters cases. This seems a bit space-wasteful
but it might be easier for clients such as pg_dump to deal with.

A possible future extension is to add some sort of direct procedure call
syntax in plpgsql. Right now, you'd need to write something like
select into x,y,z from foo(a,b,c);
to call a function with IN parameters a,b,c and get back OUT parameters
x,y,z. It would obviously be nicer to write
foo(a,b,c,x,y,z);
However, I'm inclined to wait on this until someone does
parameter-matching-by-name, for fear of creating problems for that
feature. (If people think this couldn't interfere, maybe I'll go ahead
and do it.) I'm not planning to invent new calling syntax at the SQL
level, either, since that's probably best reserved for procedures-outside-
transactions.

I will probably fix SQL-language functions to be able to work with OUT
parameters, but at least for now, the other PL languages will just
reject them. Anyone who wants to improve pltcl,plperl, etc to handle
the feature is welcome to.

Comments?

regards, tom lane