Thread: function returning record

function returning record

From
Roberto Rezende de Assis
Date:
pglista=# CREATE OR REPLACE FUNCTION teste(int,float)
pglista-# RETURNS record AS '
pglista'# DECLARE
pglista'#     numero ALIAS FOR $1;
pglista'#     margem ALIAS FOR $2;
pglista'#     retorno record;
pglista'# BEGIN
pglista'#     retorno.ponto := numero;
pglista'#     retorno.percentual := numero*margem;
pglista'#     RETURN retorno;
pglista'# END;
pglista'# ' LANGUAGE plpgsql;
CREATE FUNCTION

pglista=# SELECT teste(100,0.1);
ERROR:  record "retorno" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  PL/pgSQL function "teste" line 6 at assignment

pglista=# SELECT ponto,percentual FROM teste(100,0.1);
ERROR:  a column definition list is required for functions returning
"record"

Someone knows if it possible to return a record without creating a type
for it, or how to make that definition list that the error message show me.



Re: function returning record

From
Tom Lane
Date:
Roberto Rezende de Assis <rezende_assis@yahoo.com.br> writes:
> Someone knows if it possible to return a record without creating a type

Nope.

            regards, tom lane

Re: function returning record

From
Sean Davis
Date:
You can probably do this with another pl, like plperl or plpython, but
you will have to alias the result like below (in plperl):

create function teste(int,float) returns record as $$
   my ($numero,$margen) = @_;
   return{numero => $numero,percentual => $numero*$margen}
$$ language plperl;
CREATE FUNCTION

select * from teste(1,1.2) as t(numero int, percentual float);
  numero | percentual
--------+------------
       1 |        1.2

Sean

On May 5, 2005, at 8:34 AM, Roberto Rezende de Assis wrote:

> pglista=# CREATE OR REPLACE FUNCTION teste(int,float)
> pglista-# RETURNS record AS '
> pglista'# DECLARE
> pglista'#     numero ALIAS FOR $1;
> pglista'#     margem ALIAS FOR $2;
> pglista'#     retorno record;
> pglista'# BEGIN
> pglista'#     retorno.ponto := numero;
> pglista'#     retorno.percentual := numero*margem;
> pglista'#     RETURN retorno;
> pglista'# END;
> pglista'# ' LANGUAGE plpgsql;
> CREATE FUNCTION
>
> pglista=# SELECT teste(100,0.1);
> ERROR:  record "retorno" is not assigned yet
> DETAIL:  The tuple structure of a not-yet-assigned record is
> indeterminate.
> CONTEXT:  PL/pgSQL function "teste" line 6 at assignment
>
> pglista=# SELECT ponto,percentual FROM teste(100,0.1);
> ERROR:  a column definition list is required for functions returning
> "record"
>
> Someone knows if it possible to return a record without creating a
> type for it, or how to make that definition list that the error
> message show me.
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>