Thread: "The tuple structure of a not-yet-assigned record is indeterminate."
In my function I have (kept the important part)
CREATE OR REPLACE FUNCTION foo() RETURNS SETOF RECORD AS $$
DECLARE
retval RECORD;
BEGIN
some loop
retval.jd := tmp.id;
retval.d2 := _c2;
retval.d3 := _c3;
RETURN NEXT retval;
end loop
return;
END;
and I get
ERROR: record "retval" is not assigned yet
DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT: PL/pgSQL function "foo" line 33 at assignment
It seems that the line with retval.jd := tmp.id; is where it stops.
Any suggestion on how to fix this error?
am Mon, dem 30.06.2008, um 13:33:55 +0200 mailte A B folgendes: > In my function I have (kept the important part) > > CREATE OR REPLACE FUNCTION foo() RETURNS SETOF RECORD AS $$ > DECLARE > retval RECORD; > BEGIN > some loop > retval.jd := tmp.id; > retval.d2 := _c2; > retval.d3 := _c3; > RETURN NEXT retval; > end loop > return; > END; > > and I get > > ERROR: record "retval" is not assigned yet > DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. > CONTEXT: PL/pgSQL function "foo" line 33 at assignment > > It seems that the line with retval.jd := tmp.id; is where it stops. > Any suggestion on how to fix this error? Sure, declare your result like my example: test=# create or replace function ab() returns setof record as $$declare r record; begin select into r 1,2;return next r;end;$$languageplpgsql; CREATE FUNCTION test=*# select * from ab() as (i int, j int); i | j ---+--- 1 | 2 (1 row) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
> Sure, declare your result like my example: > > test=# create or replace function ab() returns setof record as $$declare r record; begin select into r 1,2;return nextr;end;$$language plpgsql; Unfortunatly I have not the luxury of creating the record with a single SELECT command. Isn't there a way around this problem? I really do not want to try to write it as a single SELECT command. There must be a way of telling it what structure the record will have?
am Mon, dem 30.06.2008, um 13:57:22 +0200 mailte A B folgendes: > > Sure, declare your result like my example: > > > > test=# create or replace function ab() returns setof record as $$declare r record; begin select into r 1,2;return nextr;end;$$language plpgsql; > > Unfortunatly I have not the luxury of creating the record with a > single SELECT command. You have only quotet (and read?) my function, but not the select. Again: test=*# select * from ab() as (i int, j int); i | j ---+--- 1 | 2 (1 row) Look at the part after the function-call... Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
I did read the select line also, and select * from foo() as (a integer, b integer, c integer); gives me unfortunatly the error ERROR: record "retval" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. So you are telling me this is an error that is caused by the way I call the function and not the definition of the function it self? > You have only quotet (and read?) my function, but not the select. > Again: > > test=*# select * from ab() as (i int, j int); > i | j > ---+--- > 1 | 2 > (1 row) I > > Look at the part after the function-call... > > > > Regards, Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) > GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
am Mon, dem 30.06.2008, um 14:25:30 +0200 mailte A B folgendes:
> I did read the select line also, and
> select * from foo() as (a integer, b integer, c integer);
> gives me unfortunatly the error
> ERROR: record "retval" is not assigned yet
> DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
Okay, right, i see. You can rewrite your function using IN/OUT-parameters.
For instance, your function:
CREATE OR REPLACE FUNCTION foo() RETURNS SETOF RECORD AS $$
DECLARE
retval RECORD;
BEGIN
some loop
retval.jd := tmp.id;
retval.d2 := _c2;
retval.d3 := _c3;
RETURN NEXT retval;
end loop
return;
END;
rewrite to:
CREATE OR REPLACE FUNCTION foo(OUT jd int, OUT d2 int, OUT d3 int) RETURNS SETOF RECORD AS $$
BEGIN
some loop
jd := tmp.id;
d2 := _c2;
d3 := _c3;
RETURN NEXT;
end loop;
return;
END;
*untested*.
Hope that helps, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
NGnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
I can try your suggestion, but I'd rather want to know why it doesn't work with the record, when you try to build it your self. It worked fine when you selected into the record. But speaking of that, If I try like you did: SELECT INTO retval <some expression build from the variables tmp.id, _c3, _c2 that constitutes a row> RETURN NEXT retval; wouldn't that work? I think I need some help with the syntax for the expression above... I will also try to create a datatype of my own and see if that works as a last way out. 2008/6/30 A. Kretschmer <andreas.kretschmer@schollglas.com>: > am Mon, dem 30.06.2008, um 14:25:30 +0200 mailte A B folgendes: >> I did read the select line also, and >> select * from foo() as (a integer, b integer, c integer); >> gives me unfortunatly the error >> ERROR: record "retval" is not assigned yet >> DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. > > Okay, right, i see. You can rewrite your function using IN/OUT-parameters. > > > For instance, your function: > > CREATE OR REPLACE FUNCTION foo() RETURNS SETOF RECORD AS $$ > DECLARE > retval RECORD; > BEGIN > some loop > retval.jd := tmp.id; > retval.d2 := _c2; > retval.d3 := _c3; > RETURN NEXT retval; > end loop > return; > END; > > > rewrite to: > > > CREATE OR REPLACE FUNCTION foo(OUT jd int, OUT d2 int, OUT d3 int) RETURNS SETOF RECORD AS $$ > BEGIN > some loop > jd := tmp.id; > d2 := _c2; > d3 := _c3; > RETURN NEXT; > end loop; > return; > END; > > > > *untested*. > > > Hope that helps, Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) > NGnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
As a final note, it worked fine with a custom data type! :-) No problem returning values (yet)