Choose plpgsql variable names wisely

Pavel Stehule recently wrote the post "Don't use SQL keywords as PLpgSQL variable names" describing the situation when internal stored routine variable names match PostgreSQL keywords.

But the problem is not only in keywords but also for plpgsql variable names. Consider:

CREATE TABLE human(
    name varchar,
    email varchar);

CREATE FUNCTION get_user_by_mail(email varchar) RETURNS varchar
LANGUAGE plpgsql AS 
$
DECLARE
  human varchar;
BEGIN
  SELECT name FROM human WHERE email = email INTO human;
  RETURN human;
END
$;

SELECT get_user_by_mail('foo@bar');

CREATE TABLE human(

name varchar,

email varchar);

CREATE FUNCTION get_user_by_mail(email varchar) RETURNS varchar

LANGUAGE plpgsql AS

DECLARE

human varchar;

BEGIN

SELECT name FROM human WHERE email = email INTO human;

RETURN human;

END

SELECT get_user_by_mail('foo@bar');

Output:

column reference 'email' is ambiguous
LINE 1: SELECT name FROM human WHERE email = email
                                     ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

column reference 'email' is ambiguous

LINE 1: SELECT name FROM human WHERE email = email

DETAIL: It could refer to either a PL/pgSQL variable or a table column.

OK, at least we have no hidden error like in Pavel's case. Let's try to fix it specifying an alias for the table name:

CREATE FUNCTION get_user_by_mail(email varchar) RETURNS varchar
LANGUAGE plpgsql AS 
$
DECLARE
  human varchar;
BEGIN
  SELECT name FROM human u WHERE u.email = email INTO human;
  RETURN human;
END
$;

CREATE FUNCTION get_user_by_mail(email varchar) RETURNS varchar

LANGUAGE plpgsql AS

DECLARE

human varchar;

BEGIN

SELECT name FROM human u WHERE u.email = email INTO human;

RETURN human;

END

Output:

column reference 'email' is ambiguous
LINE 1: SELECT name FROM human u WHERE u.email = email
                                                 ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

column reference 'email' is ambiguous

LINE 1: SELECT name FROM human u WHERE u.email = email

DETAIL: It could refer to either a PL/pgSQL variable or a table column.

Seems better, but still parser cannot distinguish the variable name from column name. Of course, we may use variable placeholders instead of names. So, the quick dirty fix is like:

CREATE FUNCTION get_user_by_mail(email varchar) RETURNS varchar
LANGUAGE plpgsql AS 
$
DECLARE
  human varchar;
BEGIN
  SELECT name FROM human u WHERE u.email = $1 INTO human;
  RETURN human;
END
$;

CREATE FUNCTION get_user_by_mail(email varchar) RETURNS varchar

LANGUAGE plpgsql AS

DECLARE

human varchar;

BEGIN

SELECT name FROM human u WHERE u.email = $1 INTO human;

RETURN human;

END

In addition, pay attention that human variable doesn't produce an error, even though it shares the same name with the target table. I personally do not like using $1 placeholders in code, so my suggestion would be (of course, if you don't want to change parameter name):

CREATE FUNCTION get_user_by_mail(email varchar) RETURNS varchar
LANGUAGE plpgsql AS 
$
DECLARE
  human varchar;
  _email varchar = lower(email);
BEGIN
  SELECT name FROM human u WHERE u.email = _email INTO human;
  RETURN human;
END
$;

CREATE FUNCTION get_user_by_mail(email varchar) RETURNS varchar

LANGUAGE plpgsql AS

DECLARE

human varchar;

_email varchar = lower(email);

BEGIN

SELECT name FROM human u WHERE u.email = _email INTO human;

RETURN human;

END

The same rules apply to plpgsql procedures.

To find out more about plpgsql procedures, see this blog post.

Choose plpgsql variable names wisely

Leave a Reply Cancel reply

Pavlo Golub

Blog Tags

NEWSLETTER

Articles by our PostgreSQL Experts