Re: Proposal: OUT parameters for plpgsql - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: Proposal: OUT parameters for plpgsql
Date
Msg-id Pine.LNX.4.44.0503220707190.13802-100000@kix.fsv.cvut.cz
Whole thread Raw
In response to Proposal: OUT parameters for plpgsql  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Proposal: OUT parameters for plpgsql
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: Neil Conway
Date:
Subject: Re: locks in CREATE TRIGGER, ADD FK
Next
From: strk@refractions.net
Date:
Subject: Re: caches lifetime with SQL vs PL/PGSQL procs