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,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,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])
Re: [HACKERS] How to make @id or $id as parameter name in plpgsql,
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Arnold.Zhu wrote: | 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. | Hi Arnold, Npgsql already supports parameter names starting with @. This was a feature users were requesting exactly for the porpose of helping port code from sql server to postgresql. Please, let me know of any modifications you may still need to do to get it working. I did some tests and it were working well. - -- Regards, Francisco Figueiredo Jr. Membro Fundador do Projeto MonoBrasil - MonoBrasil Project Founder Member http://monobrasil.softwarelivre.org - - "Science without religion is lame; religion without science is blind." ~ ~ Albert Einstein -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iQEVAwUBQaVATf7iFmsNzeXfAQLaMAgAgVlHRnr3amZ0kEbocnYp8tiH17fClpHv L4J/btccMlp/j4pGNiDujLKldXI9EozgzTZ1SyATw+NVTRRJqcDxA74iAfVIxA8n ucNAXwkeAMg/ko/B93ked6h+sUbHtvojz64osVmB2Uu0VmqBORVhuHYUZhGD+5EV 6iW+1BzIAuY4hVExnL3DxOaqnHs8iFRGj4l5Vns0Vzdq8c11FSAr9AUYDuO8sU4H TzVnynW56n7lotqo41+ejWU/wTBDgiic8X8ZmYYBe8VB6FZjyPiiwcIhlc3mExkw UyAIsZZlA1GhUXMWkyLrbSMV1WPa/Bq7mXy/+vtWSZ9eO0iqfOQRhA== =ha8i -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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]
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?
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 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?
On Thu, Nov 23, 2000 at 11:59:58AM +0800, Arnold.Zhu wrote: > 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. No need -- PostgreSQL 8.0 (currently in beta) already supports argument names in a function's argument list, although I think only PL/pgSQL currently does anything with them. > CREATE FUNCTION users_select_by_id(@id int4) Change @id to be a valid identifier name and it should work. You can keep using @id if you double-quote it as "@id". If that's not what you meant then please be more specific. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
