|
|
 | | From: | Arnold.Zhu | | Subject: | can plpgsql returns more flexibe value ? | | Date: | Fri, 21 Jan 2005 20:27:37 +0800 |
|
|
 | Hello, pgsql-hackers
I create a table, a type and a function like below:
------------------------------------
CREATE TABLE "User"
(
"Id" int4 NOT NULL DEFAULT nextval('public."User_Id_seq"'::text),
"Name" varchar(32) NOT NULL
);
------------------------------------
CREATE TYPE "UserSet" AS
(
"Id" int4,
"Name" varchar(32)
);
------------------------------------
CREATE OR REPLACE FUNCTION "UserSelectById"("@Id" int4)
RETURNS SETOF "User" AS
'
declare rec record;
begin
for rec in
select * from "User" where "Id" = "@Id"
loop
return next rec;
end loop;
return;
end; '
LANGUAGE 'plpgsql' VOLATILE;
------------------------------------
When I use
select * from "UserSelectById"(1);
it gives the right result set.
But when I change return type like this
------------------------------------
CREATE TYPE "UserSet" AS
(
"Id" int4,
"Name" varchar(32),
"LastLogin" timestamp --additional column
);
select * from "UserSelectById"(1) will give the following errors:
ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function "UserSelectById" line 8 at return next
This problem annoys me, if I have more than several hundred function
which returns different result set, like contains foreign keys,
I have to create many type for function's return.
Can plpgsql returns result set according to what exactly fetched,
then take return type as references to store data for return.
Whether there is some better way to deal with this problem?
Thanks & Regards
Arnold.Zhu
2005-01-21
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
|
|
| | From: | Christopher Kings-Lynne | | Subject: | Re: can plpgsql returns more flexibe value ? | | Date: | Fri, 21 Jan 2005 14:14:21 +0000 |
|
|
| > This problem annoys me, if I have more than several hundred function
> which returns different result set, like contains foreign keys,
> I have to create many type for function's return.
>
> Can plpgsql returns result set according to what exactly fetched,
> then take return type as references to store data for return.
>
> Whether there is some better way to deal with this problem?
Yeah, you just make your function return 'SETOF record' and specify the
types when you do the select:
select * from func() as (a int, b text);
Chris
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
|
|
