Re: [GENERAL] stored procedure variable names
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of inspector morse Sent: Thursday, February 19, 2015 7:58 PM To: pgsql-general@postgresql.org Subject: [GENERAL] stored procedure variable names In all other DBMS, the variable names have a distinctive character to differentiate between variables and column names: Example: SQL Server uses @ MySql uses ? Oracle uses : Firebirdsql uses : It makes it easier to write and manage queries especially in stored procedures. Just compare the below: create stored procedure get_user_for_editing(user_id int, out username varchar) begin select username into @username from users where user_id = @user_id; end; to this mess: create stored procedure get_user_for_editing(user_id int, out username varchar) begin select u.username into get_user_for_editing.username from users u where get_user_for_editing.user_id = get_user_for_editing.user_id; end; Prefixing the variables (ex: p_user_id) makes the application code harder to write as we have a lot of dynamic code that is expecting "user_id" instead of "p_user_id". Is there any plan to add a character to differentiate between variables? That: “Oracle uses :” is simply not true. There is no such requirement (mandatory prefix) in Oracale’s PlSQL. In Oracle, only when you use dynamic SQL (EXECUTE ‘…’) with “USING” (to specify variable values) – then variable inside EXECUTE ‘…’ should have ‘:’ prefix. The only difference in Postgres when using dynamic SQL variables inside EXECUTE ‘…’ are “positional” prefixed with ‘$’, eg.: $1, $2,… Regards, Igor Neyman
Re: [GENERAL] stored procedure variable names
2015-02-20 1:57 GMT+01:00 inspector morse : > In all other DBMS, the variable names have a distinctive character to > differentiate between variables and column names: > > Example: > SQL Server uses @ > MySql uses ? > Oracle uses : > Firebirdsql uses : > > It makes it easier to write and manage queries especially in stored > procedures. > > Just compare the below: > create stored procedure get_user_for_editing(user_id int, out username > varchar) > begin >select username into @username from users where user_id = @user_id; > end; > > to this mess: > > create stored procedure get_user_for_editing(user_id int, out username > varchar) > begin >select u.username into get_user_for_editing.username from users u where > get_user_for_editing.user_id = get_user_for_editing.user_id; > end; > > Prefixing the variables (ex: p_user_id) makes the application code harder > to write as we have a lot of dynamic code that is expecting "user_id" > instead of "p_user_id". > > Is there any plan to add a character to differentiate between variables? > No, and I don't think so it is necessary in this moment (so I am against a introduction new prefix) a) PostgreSQL safely solves conflicts between plpgsql and SQL - what Oracle doesn't b) Usual prefix for plpgsql variables is "_" - I don't see a difference between @,?,: Regards Pavel Stehule
Re: [GENERAL] stored procedure variable names
Yeah, I'm using plpgsql. Actually nevermind on this. I was able to patch my data access utility so it adds a prefix when calling the stored function and then remove it again before returning for front end processing. On Thu, Feb 19, 2015 at 8:44 PM, Adrian Klaver wrote: > On 02/19/2015 04:57 PM, inspector morse wrote: > >> In all other DBMS, the variable names have a distinctive character to >> differentiate between variables and column names: >> >> Example: >> SQL Server uses @ >> MySql uses ? >> Oracle uses : >> Firebirdsql uses : >> >> It makes it easier to write and manage queries especially in stored >> procedures. >> >> Just compare the below: >> create stored procedure get_user_for_editing(user_id int, out username >> varchar) >> begin >> select username into @username from users where user_id = @user_id; >> end; >> >> to this mess: >> >> create stored procedure get_user_for_editing(user_id int, out username >> varchar) >> begin >> select u.username into get_user_for_editing.username from users u >> where get_user_for_editing.user_id = get_user_for_editing.user_id; >> end; >> >> > First Postgres does not have stored procedures, but user defined > functions, so the above is a no-op right from the start. > > Second I have no idea where you are pulling get_user_for_editing.* from? > > Third, which of the Postgres procedural languages are you having an issue > with? > > Prefixing the variables (ex: p_user_id) makes the application code >> harder to write as we have a lot of dynamic code that is expecting >> "user_id" instead of "p_user_id". >> >> Is there any plan to add a character to differentiate between variables? >> > > In what procedural language? > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: [GENERAL] stored procedure variable names
On 02/19/2015 04:57 PM, inspector morse wrote: In all other DBMS, the variable names have a distinctive character to differentiate between variables and column names: Example: SQL Server uses @ MySql uses ? Oracle uses : Firebirdsql uses : It makes it easier to write and manage queries especially in stored procedures. Just compare the below: create stored procedure get_user_for_editing(user_id int, out username varchar) begin select username into @username from users where user_id = @user_id; end; to this mess: create stored procedure get_user_for_editing(user_id int, out username varchar) begin select u.username into get_user_for_editing.username from users u where get_user_for_editing.user_id = get_user_for_editing.user_id; end; First Postgres does not have stored procedures, but user defined functions, so the above is a no-op right from the start. Second I have no idea where you are pulling get_user_for_editing.* from? Third, which of the Postgres procedural languages are you having an issue with? Prefixing the variables (ex: p_user_id) makes the application code harder to write as we have a lot of dynamic code that is expecting "user_id" instead of "p_user_id". Is there any plan to add a character to differentiate between variables? In what procedural language? -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stored procedure variable names
inspector morse writes: > Is there any plan to add a character to differentiate between variables? No. You're free to use a naming convention yourself, of course, but we're not going to break every stored procedure in sight in order to impose one. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stored procedure variable names
inspector morse writes: > In all other DBMS, the variable names have a distinctive character to > differentiate between variables and column names: > > Example: > SQL Server uses @ > MySql uses ? > Oracle uses : > Firebirdsql uses : > > It makes it easier to write and manage queries especially in stored > procedures. > > Just compare the below: > create stored procedure get_user_for_editing(user_id int, out username > varchar) > begin > Â Â select username into @username from users where user_id = @user_id; > end; > > to this mess: > > create stored procedure get_user_for_editing(user_id int, out username > varchar) > begin > Â Â select u.username into get_user_for_editing.username from users u where > get_user_for_editing.user_id = get_user_for_editing.user_id; > end; > > Prefixing the variables (ex: p_user_id) makes the application code harder to > write as we have a lot of dynamic code that is expecting "user_id" instead of > "p_user_id". > > Is there any plan to add a character to differentiate between variables? Not that I'm aware of but please submit a patch or do not hesitate to run any of those other platforms where things are not such a mess :-) > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
