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

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

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

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