Hi,
 
I was wondering if a solution was ever found to the error: "wrong record type supplied in RETURN NEXT" when executing a function that returns the Record datatype? I have seen a couple of previous post from Tom Lane and others, but no real resolution.
 
I am attempting to execute this code and I get that error:
 
<code>
CREATE FUNCTION tester(varchar) RETURNS SETOF record as'
DECLARE
ft record;
begin
      FOR ft IN SELECT * FROM visaapplicants LOOP
          RETURN NEXT ft;
      END LOOP;
return null;
end;'
  LANGUAGE 'plpgsql' VOLATILE;
 
<calling query>
select * from tester('a') as (c varchar);
 
<exact error>
ERROR:  wrong record type supplied in RETURN NEXT
CONTEXT:  PL/pgSQL function "tester" line 6 at return next
 
It was just a simple test statement because a larger one is producing the same error, and when I change return next to return it does not return any data even though there are 500 records.
 
TIA
 
Alex Erwin

Hi,
 
I was wondering if a solution was ever found to the error: "wrong record type supplied in RETURN NEXT" when executing a function that returns the Record datatype? I have seen a couple of previous post from Tom Lane and others, but no real resolution.
 
I am attempting to execute this code and I get that error:
 
<code>
CREATE FUNCTION tester(varchar) RETURNS SETOF record as'DECLAREft record;
begin      FOR ft IN SELECT * FROM visaapplicants LOOP          RETURN NEXT ft;      END LOOP;return null;end;'  LANGUAGE 'plpgsql' VOLATILE;
 
<calling query>
select * from tester('a') as (c varchar);
 
<exact error>
ERROR:  wrong record type supplied in RETURN NEXTCONTEXT:  PL/pgSQL function "tester" line 6 at return next
 
It was just a simple test statement because a larger one is producing the same error, and when I change return next to return it does not return any data even though there are 500 records.
 
TIA
 
Alex Erwin
Sat, 27 Dec 2003 11:48:43 -0800 (PST)
A E <cooljoint@yahoo.com>
Error with returning SETOF Record
A E <cooljoint@yahoo.com> writes:
> CREATE FUNCTION tester(varchar) RETURNS SETOF record as'
> DECLARE
> ft record;
> begin
>       FOR ft IN SELECT * FROM visaapplicants LOOP
>           RETURN NEXT ft;
>       END LOOP;
> return null;
> end;'
>   LANGUAGE 'plpgsql' VOLATILE;
 
> <calling query>
> select * from tester('a') as (c varchar);
 
> <exact error>
> ERROR:  wrong record type supplied in RETURN NEXT
> CONTEXT:  PL/pgSQL function "tester" line 6 at return next

That's pretty much what I'd expect, unless table visaapplicants contains
only a single varchar column.  The example works fine for me if I define
visaapplicants that way.

			regards, tom lane

Sat, 27 Dec 2003 16:19:17 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Error with returning SETOF Record 
I guess the question I have then is how do I return data from a select statement using the record datatype? This is only a test function I was trying to get info from, but my application is based on passing the table name to the function dynamically. How do I do this? The documentation is sketchy when it comes to the record datatype.
 
TIA
Alex

Tom Lane <tgl@sss.pgh.pa.us> wrote: 
A E writes:
> CREATE FUNCTION tester(varchar) RETURNS SETOF record as'
> DECLARE
> ft record;
> begin
> FOR ft IN SELECT * FROM visaapplicants LOOP
> RETURN NEXT ft;
> END LOOP;
> return null;
> end;'
> LANGUAGE 'plpgsql' VOLATILE;

> 
> select * from tester('a') as (c varchar);

> 
> ERROR: wrong record type supplied in RETURN NEXT
> CONTEXT: PL/pgSQL function "tester" line 6 at return next

That's pretty much what I'd expect, unless table visaapplicants contains
only a single varchar column. The example works fine for me if I define
visaapplicants that way.

regards, tom lane

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


I guess the question I have then is how do I return data from a select statement using the record datatype? This is only a test function I was trying to get info from, but my application is based on passing the table name to the function dynamically. How do I do this? The documentation is sketchy when it comes to the record datatype.
 
TIA
AlexTom Lane <tgl@sss.pgh.pa.us> wrote: 
A E writes:> CREATE FUNCTION tester(varchar) RETURNS SETOF record as'> DECLARE> ft record;> begin> FOR ft IN SELECT * FROM visaapplicants LOOP> RETURN NEXT ft;> END LOOP;> return null;> end;'> LANGUAGE 'plpgsql' VOLATILE;> > select * from tester('a') as (c varchar);> > ERROR: wrong record type supplied in RETURN NEXT> CONTEXT: PL/pgSQL function "tester" line 6 at return nextThat's pretty much what I'd expect, unless table visaapplicants containsonly a single varchar column. The example works fine for me if I definevisaapplicants that way.regards, tom lane---------------------------(end of broadcast)---------------------------TIP 9: the planner will ignore your desire to choose an in
 dex scan
 if yourjoining column's datatypes do not match
Sat, 27 Dec 2003 13:57:29 -0800 (PST)
A E <cooljoint@yahoo.com>
Re: Error with returning SETOF Record 
A E wrote:
> I guess the question I have then is how do I return data from a
> select statement using the record datatype? This is only a test
> function I was trying to get info from, but my application is based
> on passing the table name to the function dynamically. How do I do
> this? The documentation is sketchy when it comes to the record
> datatype.

You need to specify in your query the column definition that will 
actually be returned. Here's a simple example:

create table t1 (f1 int, f2 text);
insert into t1 values (1,'a');
insert into t1 values (2,'b');
create table t2 (f1 int, f2 float8);
insert into t2 values (3, 3.14);
insert into t2 values (4, 2.8);

create or replace function getrec(text) returns setof record as '
DECLARE
   ft record;
begin
   FOR ft IN EXECUTE ''SELECT * FROM '' || $1  LOOP
       RETURN NEXT ft;
   END LOOP;
   return;
end;
' LANGUAGE 'plpgsql' VOLATILE;

regression=# SELECT * FROM getrec('t1') AS (f1 int, f2 text);
  f1 | f2
----+----
   1 | a
   2 | b
(2 rows)

regression=# SELECT * FROM getrec('t2') AS (f1 int, f2 float8);
  f1 |  f2
----+------
   3 | 3.14
   4 |  2.8
(2 rows)

HTH,

Joe



Sat, 27 Dec 2003 15:24:18 -0800
Joe Conway <mail@joeconway.com>
Re: Error with returning SETOF Record
On Sat, 27 Dec 2003, A E wrote:

> Hi,
>  
> I was wondering if a solution was ever found to the error: "wrong record type supplied in RETURN NEXT" when executing a function that returns the Record datatype? I have seen a couple of previous post from Tom Lane and others, but no real resolution.
>  
> I am attempting to execute this code and I get that error:
>  
> <code>
> CREATE FUNCTION tester(varchar) RETURNS SETOF record as'
> DECLARE
> ft record;
> begin
>       FOR ft IN SELECT * FROM visaapplicants LOOP
>           RETURN NEXT ft;
>       END LOOP;
> return null;

You have to write only return!


> end;'
>   LANGUAGE 'plpgsql' VOLATILE;
>  
Regards Pavel


Mon, 29 Dec 2003 09:31:09 +0100 (CET)
Pavel Stehule <stehule@kix.fsv.cvut.cz>
Re: Error with returning SETOF Record