Thread: return two elements

Hello,

how can I write a function that return two or more elements?
I try to return a record but after I don't know how to assign returned value to a variable. For example,

CREATE OR REPLACE FUNCTION test1() RETURNS record AS '
declare
        rec record;
begin
        select into rec \'01/01/2005\'::Date as dt_inici, \'02/02/2005\'::Date as dt_fi;
        return rec;
end; '
LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION test2() RETURNS date AS '
declare
        rec record;
begin
        -- rec := test1();                      << ERROR:  syntax error at or near "rec"
        select into rec test1();
        return rec.dt_inici;            <<  ERROR: record "rec" has no field "dt_inici"
end; '
LANGUAGE 'plpgsql' VOLATILE;

Can I return an array?, and how I take returned array?
I search postgres documentation but I don't find it

Thanks in advance,

pere

You could use your own type, since it seems you know what values you'll be storing there. You can create it like this:

CREATE TYPE MyResult AS (dt_inici DATE,dt_fi DATE
);

Then you must change your functions as follows:

CREATE OR REPLACE FUNCTION test1() RETURNS MyResult AS '
declarer MyResult;
beginr.dt_inici:=\'01/01/2005\'::Date;r.dt_fi:=\'02/02/2005\'::Date;RETURN r;
end; '
LANGUAGE 'plpgsql' VOLATILE; 


CREATE OR REPLACE FUNCTION test2() RETURNS date AS '
declarer MyResult;
beginr:=test1();   RETURN r.dt_inici;
end; '
LANGUAGE 'plpgsql' VOLATILE; 

To me, using a TYPE seems clearer than using an array in this case.

=?ISO-8859-1?Q?=22Rodr=EDguez_Rodr=EDguez=2C_Pere=22?= <prr@hosppal.es> writes:
> how can I write a function that return two or more elements?

In existing releases the best way to do this is to create a named
composite type and have the function return that, not RECORD.

            regards, tom lane

You could use your own type, since it seems you know what values you'll be storing there. You can create it like this:

CREATE TYPE MyResult AS (dt_inici DATE,dt_fi DATE
);

Then you must change your functions as follows:

CREATE OR REPLACE FUNCTION test1() RETURNS MyResult AS '
declarer MyResult;
beginr.dt_inici:=\'01/01/2005\'::Date;r.dt_fi:=\'02/02/2005\'::Date;RETURN r;
end; '
LANGUAGE 'plpgsql' VOLATILE; 


CREATE OR REPLACE FUNCTION test2() RETURNS date AS '
declarer MyResult;
beginr:=test1();   RETURN r.dt_inici;
end; '
LANGUAGE 'plpgsql' VOLATILE; 

To me, using a TYPE seems clearer than using an array in this case.

In future releases is planned to incorporate IN/OUT declaration in parameters of user functions?

-----Mensaje original-----
De: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Enviado el: lunes 6 de junio de 2005 17:10
Para: "Rodríguez Rodríguez, Pere"
CC: pgsql-general@postgresql.org
Asunto: Re: [GENERAL] return two elements

"Rodríguez Rodríguez, Pere" <prr@hosppal.es> writes:
> how can I write a function that return two or more elements?

In existing releases the best way to do this is to create a named
composite type and have the function return that, not RECORD.

                        regards, tom lane

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

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

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@fuhr.org]
Enviado el: martes 7 de junio de 2005 09:30
Para: Rodríguez Rodríguez,Pere
CC: pgsql-general@postgresql.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-PARAMETERS

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

For my it would be sufficient that I could return a basic type in OUT/INOUT parameters, if in addition I could return a set .... fantastic!

-----Mensaje original-----
De: Alvaro Herrera [mailto:alvherre@surnet.cl]
Enviado el: martes 7 de junio de 2005 19:22
Para: Michael Fuhr
CC: Rodríguez Rodríguez,Pere; pgsql-general@postgresql.org
Asunto: Re: [GENERAL] return two elements

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)

On Wed, Jun 08, 2005 at 01:28:56AM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@surnet.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@hosppal.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@surnet.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)

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@surnet.cl]
Enviado el: miércoles 8 de junio de 2005 17:53
Para: Michael Fuhr
CC: Tom Lane; pgsql-general@postgresql.org
Asunto: Re: [GENERAL] return two elements

I dropped prr@hosppal.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@surnet.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

On Fri, Jun 10, 2005 at 02:24:40PM +0200, "Rodríguez Rodríguez, Pere" wrote:
> 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?

No, that's exactly what I was saying it's not supported.

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
Y una voz del caos me habló y me dijo
"Sonríe y sé feliz, podría ser peor".
Y sonreí. Y fui feliz.
Y fue peor.