From: | Igor Neyman <ineyman(at)perceptron(dot)com> |
---|---|
To: | inspector morse <inspectormorse86(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: stored procedure variable names |
Date: | 2015-02-20 15:11:42 |
Message-ID: | A76B25F2823E954C9E45E32FA49D70ECC2308810@mail.corp.perceptron.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of inspector morse
Sent: Thursday, February 19, 2015 7:58 PM
To: pgsql-general(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | sridhar bamandlapally | 2015-02-20 15:26:04 | Oracle to PostgreSQL migration |
Previous Message | Guillaume Drolet | 2015-02-20 12:12:06 | Re: Cluster seems broken after pg_basebackup |