Postgres Pro
Downloads
Home   >   mailing lists

Thread: stored procedure variable names

stored procedure variable names

From
inspector morse
Date:
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?

Re: stored procedure variable names

From
Jerry Sievers
Date:
inspector morse <inspectormorse86@gmail.com> writes:

> 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
thatis expecting "user_id" instead of "p_user_id". 
>
> Is there any plan to add a character to differentiate between variables?

Not that I'm aware of but please submit a patch or do not hesitate to
run any of those other platforms where things are not such a mess :-)

>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

Re: stored procedure variable names

From
Tom Lane
Date:
inspector morse <inspectormorse86@gmail.com> writes:
> Is there any plan to add a character to differentiate between variables?

No.  You're free to use a naming convention yourself, of course, but
we're not going to break every stored procedure in sight in order
to impose one.

            regards, tom lane

Re: stored procedure variable names

From
Adrian Klaver
Date:
On 02/19/2015 04:57 PM, inspector morse wrote:
> 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;
>

First Postgres does not have stored procedures, but user defined
functions, so the above is a no-op right from the start.

Second I have no idea where you are pulling get_user_for_editing.* from?

Third, which of the Postgres procedural languages are you having an
issue with?

> 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?

In what procedural language?


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: stored procedure variable names

From
inspector morse
Date:
Yeah, I'm using plpgsql.

Actually nevermind on this. I was able to patch my data access utility so it adds a prefix when calling the stored function and then remove it again before returning for front end processing.

On Thu, Feb 19, 2015 at 8:44 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 02/19/2015 04:57 PM, inspector morse wrote:
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;


 First Postgres does not have stored procedures, but user defined functions, so the above is a no-op right from the start.

Second I have no idea where you are pulling get_user_for_editing.* from?

Third, which of the Postgres procedural languages are you having an issue with?

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?

 In what procedural language?


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: stored procedure variable names

From
Pavel Stehule
Date:


2015-02-20 1:57 GMT+01:00 inspector morse <inspectormorse86@gmail.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

Re: stored procedure variable names

From
Igor Neyman
Date:

 

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@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