Re: stored procedure variable names

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

2015-02-20 1:57 GMT+01:00 inspector morse <inspectormorse86@xxxxxxxxx>:

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