Tom Lane <tgl@sss.pgh.pa.us> writes:

> > I would have expected the return value to be an extra column added to the
> > record.
>
> I'd prefer not to do that, because having a "return type" that's
> different from the true return type of the function (ie the RECORD)
> is going to cause untold amounts of confusion.

Yes, I can see that angle. I was just thinking that since the whole point of
this exercise was to achieve some compatibility with a specific interface that
your hands were going to be tied.

But that other point about other systems only allowing IN or INOUT on
procedures where normal return values aren't allowed at all seems to resolve
that issue.

--
greg


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
> On Mon, 21 Mar 2005, Tom Lane wrote:
>> So typical call style would be
>> SELECT * FROM foo(1,2,'xyzzy');

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

Well, what I am after here is basically to try to fix the function
definition style to be more standard. The SELECT FROM calling style is
admittedly not standard, but it's what we already support and will have
to continue to support indefinitely. We can go back and work on that
end of things in the future; I don't think this proposal forecloses
anything as far as other call syntaxes go.

> For anonymous out record (not OUT parameters) is better Firebird syntax
>
> CREATE FUNCTION fce (...) RETURNS (c1 integer, c2 integer) AS

That might be more readable, but it's not standard and not flexible
(no INOUT parameters), so I don't really see the advantage.

> When I use OUT params I have to have DECLARE command for variables
> DECLARE b integer;

That's basically what I want to avoid, for the time being at least.
Variables in straight SQL don't make any sense to me: variables go with
conventional, imperative programming languages and SQL really isn't one.
A variable for an OUT result should live in some program that is calling
SQL, which means it's a feature for client-side code or a feature that
exists inside a PL.

The advantage of what I am proposing is basically that we can create
functions that return OUT parameters without having to buy into
inventing SQL variables. We can always do that later if we decide
we want to.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster