Thread: can plpgsql returns more flexibe value ?
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
> 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
Hello, Christopher Kings-Lynne >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 > This is not a good idea when I use C# to program, I want to Fill the resultset directly into Dataset, this method will should use more sql to get data, it lose function's convenience like stored procedure. Thanks & Regards! Arnold.Zhu 2005-01-22
On Sat, 22 Jan 2005, Arnold.Zhu wrote: > >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); > > > > This is not a good idea when I use C# to program, I want to Fill the resultset > directly into Dataset, this method will should use more sql to get data, > it lose function's convenience like stored procedure. > Perhaps you should look into the refcursor type, which will allow you to return anything you want without specifying it. You can't do things like a join between to refcursor outputs, but it does allow for more return flexibility. Kris Jurka
Hello, Kris Jurka! > >Perhaps you should look into the refcursor type, which will allow you to >return anything you want without specifying it. You can't do things like >a join between to refcursor outputs, but it does allow for more return >flexibility. > >Kris Jurka > Can I use DataAdapter.Fill() with refcursor. :-( I don't want to fetch data one bye one. Thanks & Regards Arnold.Zhu joint@shaucon.com 2005-01-23
On Sun, 23 Jan 2005, Arnold.Zhu wrote: > Can I use DataAdapter.Fill() with refcursor. :-( I have no idea what DataAdapter is, you will need to check your client interface for support (and this probably isn't the place to do that), but it's certainly possible. See for example "The world's most advanced PostgreSQL client interface" http://jdbc.postgresql.org/documentation/80/ch06.html#callproc-resultset-refcursor Kris Jurka
Hello, Kris Jurka Thank you for your reply, I will go to Npgsql development team for help. >I have no idea what DataAdapter is, you will need to check your client >interface for support (and this probably isn't the place to do that), but >it's certainly possible. See for example "The world's most advanced >PostgreSQL client interface" > >http://jdbc.postgresql.org/documentation/80/ch06.html#callproc-resultset-refcursor > >Kris Jurka > Arnold.Zhu joint@shaucon.com 2005-01-24