Re: stored procedure variable names

2015-02-20 1:57 GMT+01:00 inspector morse <inspectormorse86(at)gmail(dot)com>:

> 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