Re: Proposal: OUT parameters for plpgsql

On Mon, 21 Mar 2005, Tom Lane wrote:

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

I am not sure so this syntax is readable. I'm sure, so this solution is
possible and usefull, but you mix SRF style of calling and normal style.

For anonymous out record (not OUT parameters) is better Firebird syntax

CREATE FUNCTION fce (...) RETURNS (c1 integer, c2 integer) AS
BEGIN
c1 := 10; c2 := 20;
RETURN;
END;

SELECT * FROM fce (...);
c1 | c2
-------
10 | 20

There is on first view clear which calling style I have to use. This is
very similar you proposal - one difference - all OUT params are separeted
into return's list.

Or clasic SP

CREATE FUNCTION fce (IN a integer, OUT b integer) RETURNS bool AS
BEGIN
b := a;
RETURN 't';
END;

When I use OUT params I have to have DECLARE command for variables

DECLARE b integer;
SELECT fce(10, b);
fce
---
t
SELECT b;
b
--
10

This is (I think) more standard behavior.

Regards
Pavel Stehule