Thread: How to return a record set from function.

Kumar wrote:
> Create table t1 (c1 int, c2 varchar, c3 varchar);
> 
> Create or Replace function sel_t1 () returns setof records as ' 
> select c1, c2, c3 from t1; ' Language SQL;
> 
> It was fine and created a function. while i execute it as
> 
> select sel_t1;
> 
> I got the following error.
> 
> ERROR:  Cannot display a value of type RECORD

I see three problems.

1) you need parenthesis on the function call, i.e. "sel_t1()" as   compared with "sel_t1"
2) when returning setof record, the "sel_t1()" must be in the FROM   clause of the statement
3) when the function is declared as returning "record" as compared to   a named complex type such as "t1", you need to
includea column   definition list in the SQL statement
 

So, putting it all together, try something like this:

SELECT f1, f2, f3 FROM sel_t1() AS (f1 int, f2 varchar, f3 varchar);

See:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=xfunc-tablefunctions.html
and
http://techdocs.postgresql.org/guides/SetReturningFunctions

HTH,

Joe

On Wed, 27 Aug 2003, Kumar wrote:

> Dear Friends,
>
> I am newbie to Postgres. I am running 7.3.4 on Linux 7.3. I am using
> Pgadmin tool. I need to return the table rows via record set.
>
> Create table t1 (c1 int, c2 varchar, c3 varchar);
>
> Create or Replace function sel_t1 () returns setof records as '

Why not setof t1?

> select c1, c2, c3 from t1;
> ' Language SQL;
>
> It was fine and created a function. while i execute it as
>
> select sel_t1;

You probably want
select *   from sel_t1() as tab(c1 int, c2 varchar, c3 varchar)
(if you return setof record)

or

select * from sel_t1();
(if you return setof t1)

On Wednesday 27 August 2003 08:18, Kumar wrote:
> Dear Friends,
>
> I am newbie to Postgres. I am running 7.3.4 on Linux 7.3. I am using
> Pgadmin tool. I need to return the table rows via record set.
>
> Create table t1 (c1 int, c2 varchar, c3 varchar);
>
> Create or Replace function sel_t1 () returns setof records as '
> select c1, c2, c3 from t1;
> ' Language SQL;
>
> It was fine and created a function. while i execute it as
>
> select sel_t1;
>
>  I got the following error.
>
> ERROR:  Cannot display a value of type RECORD

You probably want to return "setof t1" and then do:

SELECT * FROM sel_t1();

--  Richard Huxton Archonet Ltd