pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to return a record set from function.
4+ messages / 4 participants
[nested] [flat]
* How to return a record set from function.
@ 2003-08-27 07:18 Kumar <sgnerd@yahoo.com.sg>
0 siblings, 3 replies; 4+ messages in thread
From: Kumar @ 2003-08-27 07:18 UTC (permalink / raw)
To: pgsql-sql
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
How to solve this. Please help.
Kumar
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to return a record set from function.
@ 2003-08-27 13:50 Richard Huxton <dev@archonet.com>
parent: Kumar <sgnerd@yahoo.com.sg>
2 siblings, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2003-08-27 13:50 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to return a record set from function.
@ 2003-08-27 15:11 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Kumar <sgnerd@yahoo.com.sg>
2 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2003-08-27 15:11 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to return a record set from function.
@ 2003-08-28 01:02 Joe Conway <mail@joeconway.com>
parent: Kumar <sgnerd@yahoo.com.sg>
2 siblings, 0 replies; 4+ messages in thread
From: Joe Conway @ 2003-08-28 01:02 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; +Cc: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-08-28 01:02 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-27 07:18 How to return a record set from function. Kumar <sgnerd@yahoo.com.sg>
2003-08-27 13:50 ` Richard Huxton <dev@archonet.com>
2003-08-27 15:11 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-28 01:02 ` Joe Conway <mail@joeconway.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox