From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | Kumar <sgnerd(at)yahoo(dot)com(dot)sg> |
Cc: | psql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: How to return a record set from function. |
Date: | 2003-08-28 01:02:38 |
Message-ID: | 3F4D54AE.3030604@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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 include a 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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-08-28 01:18:39 | Re: How to optimize this query ? |
Previous Message | ProgHome | 2003-08-28 01:01:27 | Re: How to optimize this query ? |