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
