Alias plpgsql variables

Inside plpgsql function you can often get naming conflicts between input parameters and column / table names

My previous modus operandi was to qualify the input param using the function name. For example:

Schema

create table data.user (
  id serial primary key;
  name text not null;
  birth_date date not null;
);

Options

Qualify using function name

create function age( "user" int ) returns int as 
$$
  declare
    user_id alias for "user";
    --  user_id alias for $1; -- alternative
  begin
    select age(current_date, birth_date) from data.user where id = age.user ;
  end;
$$ language plpgsql;

ALIAS

But you can also use ALIAS FOR:

create function age( "user" int ) returns int as 
$$
  declare
    user_id alias for "user";
    --  user_id alias for $1; -- alternative
  begin
    select age(current_date, birth_date) from data.user where id = user_id ;
  end;
$$ language plpgsql;

Aside: For a function that only does a single select a SQL language function is more appropriate.

Created 2022-11-24T04:25:19-05:00, updated 2022-11-24T04:27:05-05:00 · History · Edit

Alias plpgsql variables

Schema

Options

Qualify using function name

ALIAS

Related