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

> 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