Re: return two elements

I don't know that it happens with my email I will change the email of my
subscription.

The examples are very interesting for my, and Alvaro Herrera's comments too.

In reference to INOUT/OUT params and return a set I have a doubt: I will be
able to return a set of table row type and return INOUT/OUT params?. For
example,

CREATE TABLE foo_table
(
id int4;
dsc varchar(20;
);

CREATE FUNCTION foo(INOUT y integer, OUT z integer) RETURNS SETOF foo_table
AS $$
DECLARE
row foo_table%ROWTYPE;
BEGIN
z := y + 1;
y := 33;

row.id := 1;
row.dsc := 'dsc 1';
retrun next row;

row.id := 2;
row.dsc := 'dsc 2';
retrun next row;

return;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM foo(1,2);
id | dsc
----+-------
1 | dsc 1
2 | dsc 2

In the example return next works like now and in addition function has a
INOUT/OUT params that acts as host variables for interact with others
functions.

pere

-----Mensaje original-----
De: Alvaro Herrera [mailto:alvherre(at)surnet(dot)cl]
Enviado el: miércoles 8 de junio de 2005 17:53
Para: Michael Fuhr
CC: Tom Lane; pgsql-general(at)postgresql(dot)org
Asunto: Re: [GENERAL] return two elements

I dropped prr(at)hosppal(dot)es from the Cc: because that account has serious
issues.

On Wed, Jun 08, 2005 at 08:16:32AM -0600, Michael Fuhr wrote:
> On Wed, Jun 08, 2005 at 01:28:56AM -0400, Tom Lane wrote:
> > Alvaro Herrera <alvherre(at)surnet(dot)cl> writes:
> > > Hmm, be aware that you can't return a set if you have OUT/INOUT
> > > parameters.
> >
> > ? News to me --- what are you worried about exactly?
> >
> > It's surely possible that our idea of what this means is different
> > from Oracle's, but we ought to take a close look before the semantics
> > get set in stone by a release ...

My point is that Oracle and others, you can have an OUT parameter to
return, say a number, and additionally a set like those returned with
RETURN NEXT. And both things are independent.

> The following example works in HEAD:
>
> CREATE FUNCTION foo(INOUT y integer, OUT z integer) RETURNS SETOF record
AS $$
> BEGIN
> y := y + 1; z := y + 2; RETURN NEXT;
> y := y + 1; z := z + 3; RETURN NEXT;
> y := y + 1; z := z + 4; RETURN NEXT;
> END;
> $$ LANGUAGE plpgsql;
>
> SELECT * FROM foo(1);
> y | z
> ---+----
> 2 | 4
> 3 | 7
> 4 | 11
> (3 rows)

Yeah, but if you do that, you can't use the OUT parameter separately.
My point is that something like this doesn't work:

CREATE FUNCTION foo (OUT y) RETURNS SETOF int LANGUAGE plpgsql AS $$
DECLARE
z INT;
BEGIN
y := 4;
FOR z IN 1 .. 3 LOOP
RETURN NEXT z;
END LOOP;
END;
$$

Now, this approach has a problem, and it's where do you save the value
of y? We have no "host variables." This is exactly the reason Tom
punted and made it return OUT/INOUT params in the result set, at the
same time prohibiting it from receiving further output.

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"Llegará una época en la que una investigación diligente y prolongada sacará
a la luz cosas que hoy están ocultas" (Séneca, siglo I)

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match