Обсуждение: 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