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
