stored procedure variable names

From: inspector morse <inspectormorse86(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: stored procedure variable names
Date: 2015-02-20 00:57:54
Message-ID: CAHYn==63WqKYRSQGwmtaNa1uHO4SqAPu8LooPmbubJHfsjxxxg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jerry Sievers 2015-02-20 01:16:49 Re: stored procedure variable names
Previous Message Juan Pablo L 2015-02-19 22:42:34 Re: rollback in C functions