Re: [HACKERS] can plpgsql returns more flexibe value ?
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 [EMAIL PROTECTED] 2005-01-24 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] can plpgsql returns more flexibe value ?
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 [EMAIL PROTECTED] 2005-01-23 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] 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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] can plpgsql returns more flexibe value ?
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] How to make @id or $id as parameter name in plpgsql,isit available?
Hello, Francisco Figueiredo Jr. yes, I'd like to. Thank you for talking with you. ^_^ H, could you add a feature request in Npgsql project? This feature may be helpful for other users who are porting their apps to postgresql :) Also, if possible, please add a simple test case with the expected result so we can work on it. Thanks Regards! Arnold.Zhu 2000-11-26 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] How to make @id or $id as parameter name in plpgsql,isit available?
following is only program fragment, original program structure is from sample
named Duwamish in ms vs.net 2002.
/
private const String ID_PARM= @id;
private const String NAME_PARM = @name;
public UserData GetUserById(int id)
{
if ( dataAdapter == null )
{
throw new System.ObjectDisposedException( GetType().FullName );
}
UserData userData = new UserData();
dataAdapter.SelectCommand = GetUserByIdCommand();
dataAdapter.SelectCommand.Parameters[ID_PARM].Value = id;
dataAdapter.Fill(data);
return userData;
}
private SelectCommand GetUserByIdCommand()
{
if ( getUserCommand == null)
{
selectUserByIdCommand = new SelectCommand(user_select_by_id,
Configuration.ConnectDB());
selectUserByIdCommand.CommandType = CommandType.StoredProcedure;
ParameterCollection params = selectUserByIdCommand.Parameters;
params.Add(new Parameter(ID_PARM, DbType.Int32));
}
return selectUserByIdCommand;
}
/
-
CREATE TABLE users (
id serial NOT NULL,
name character varying(32) NOT NULL
);
-
CREATE TYPE user_set AS (
id integer,
name character varying(32)
);
-
CREATE FUNCTION user_select_by_id(@id int4)
RETURNS SETOF user_set
AS '
declare rec record;
begin
for rec in
select * from users where id = @id
loop
return next rec;
end loop;
return;
end; '
LANGUAGE plpgsql;
-
Thanks Regards!
Arnold.Zhu
2004-11-26
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] How to make @id or $id as parameter name in plpgsql,is it available?
Hello, Francisco Figueiredo Jr. plpgsql did not support @id as parameter(need double-quote), somebody suggest me to use vid for parameter. When use vid as parameter for plpgsql, in C# program we use @vid, Npgql will delete @, then pass vid to plpgsql. So I want to change Npgsql not to delete @ in program. Thanks Regards! Arnold.Zhu 2000-11-25 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] How to make @id or $id as parameter name in plpgsql, is it available?
Hello, pgsql-hackers Can I change postgresql's source to make the following plpgsql works ? If could, would you please tell me where can i change the source? I want to try it. --- CREATE FUNCTION users_select_by_id(@id int4) RETURNS SETOF users_set AS ' declare rec record; begin for rec in select * from users where id = @id loop return next rec; end loop; return; end; 'LANGUAGE plpgsql; --- Thanks Regards Arnold.Zhu 2000-11-23 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] How to make @id or $id as parameter name in plpgsql, is it available?
Hello, Michael Fuhr Thank you very much for your reply, I'm looking for a method to port our application with .Net and Sql Server 2000 to linux and mono. Sql server 2000's stored procedure take parameter like @Id, @Name etc. Then in our C# programs, use @Id, @Name as Select, Insert, Delete, Update commands' parameter name. I don't want to change our C# source and stored procedure too much. It contains about 50M of C# source files, and more than 1000 stored procedures. I want to use @id, @name as plpgsql's parameter, then I've no need to change C# source, only change Npgsql driver not to trim @ and stored procedure to plpgsql. Is my description clear enough to express my thought? Waiting for your further replys. Thanks Regards! Arnold.Zhu 2004-11-24 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] How to make @id or $id as parameter name in plpgsql, is it available?
Hello, Michael Fuhr I've changed my clock. Back to future now. ^_^ Thanks Regards! Arnold.Zhu 2004-11-24 === 2004-11-24 13:52:05 You wrote: === On Tue, Nov 23, 2004 at 10:46:54PM -0700, Michael Fuhr wrote: On Thu, Nov 23, 2000 at 11:59:58AM +0800, Arnold.Zhu wrote: Ummm...did you know your clock was four years behind? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ = = = = = = = = = = = = = = = = = = = = ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
