Re: return two elements

Ok, :)

I suppose that IN/OUT declaration also will use with procedural language
(PL/pgSQL), it's correct?

-----Mensaje original-----
De: Michael Fuhr [mailto:mike(at)fuhr(dot)org]
Enviado el: martes 7 de junio de 2005 09:30
Para: Rodríguez Rodríguez,Pere
CC: pgsql-general(at)postgresql(dot)org
Asunto: Re: [GENERAL] return two elements

On Tue, Jun 07, 2005 at 09:00:28AM +0200, "Rodríguez Rodríguez, Pere" wrote:
>
> In future releases is planned to incorporate IN/OUT declaration in
> parameters of user functions?

Yes -- it's already in the development code.

http://developer.postgresql.org/docs/postgres/xfunc-sql.html#XFUNC-OUTPUT-PA
RAMETERS

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Tue, Jun 07, 2005 at 10:08:27AM +0200, "Rodríguez Rodríguez, Pere" wrote:
>
> I suppose that IN/OUT declaration also will use with procedural language
> (PL/pgSQL), it's correct?

Yes; INOUT is also supported. The following link has examples of
PL/pgSQL functions that use OUT:

http://developer.postgresql.org/docs/postgres/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Tue, Jun 07, 2005 at 07:45:03AM -0600, Michael Fuhr wrote:
> On Tue, Jun 07, 2005 at 10:08:27AM +0200, "Rodríguez Rodríguez, Pere" wrote:
> >
> > I suppose that IN/OUT declaration also will use with procedural language
> > (PL/pgSQL), it's correct?
>
> Yes; INOUT is also supported.

Hmm, be aware that you can't return a set if you have OUT/INOUT
parameters. Apparently this is something people coming from Oracle/SQL
Server expect to be able to do.

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"Cada quien es cada cual y baja las escaleras como quiere" (JMSerrat)

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

regards, tom lane

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

I see the following in the development documentation -- are the
semantics still under discussion? Should this thread be moved to
pgsql-hackers?

"If you declared the function with output parameters, write just
RETURN NEXT with no expression. The current values of the output
parameter variable(s) will be saved for eventual return. Note that
you must declare the function as returning SETOF record when there
are multiple output parameters, or SETOF sometype when there is
just one output parameter of type sometype, in order to create a
set-returning function with output parameters."

http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

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)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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)