CODEWITHSUNDEEP
postgresqlplpgsqlpostgresql-9.5
Moseleyi
Moseleyi

Reputation: 2879

PostgreSQL - Function with local variables - Column reference is ambiguous

I have had a look at other questions that match my subject but in my case the ambiguity, I believe, comes from variable having the same name as a column.

Here is a simplified version of the function I am trying to create:

CREATE OR REPLACE FUNCTION get_user_id(username TEXT) 
RETURNS INTEGER AS $$
  DECLARE
    user_id BIGINT;
    other_param TEXT;
  BEGIN
    SELECT INTO user_id user_id FROM users WHERE users.username = get_user_id.username;

    SELECT INTO other_param users.value FROM users WHERE users.user_id = user_id;

    RETURN user_id;
  END
$$ LANGUAGE PLPGSQL

The problem is that the user_id on the right hand side of the WHERE is treated as a reference to the column.

I definitely need user_id in a local variable as it will be used in UPDATE and DELETE operations further along the function, but I won't get the it passed as a parameter, only username.

Upon some reading and having previously had some problems with parameters too I realised I can use get_user_id.username but it only applies to parameters, not local variables (if I use it with variable the query with fail as there is not FROM-clause for get_user_id).

Therefore am I missing something (not so) obvious about using variables in queries inside a function?

--- edit

Apologies I oversimplified the function, of course there's a first query that gets the user_id, and that part is working fine.

Upvotes: 6

Views: 5098

Answers (4)

Vao Tsun
Vao Tsun

Reputation: 51659

https://www.postgresql.org/docs/current/static/plpgsql-implementation.html

Sometimes it is impractical to fix all the ambiguous references in a large body of PL/pgSQL code. In such cases you can specify that PL/pgSQL should resolve ambiguous references as the variable

and so on... thus:

t=# CREATE OR REPLACE FUNCTION get_user_id(username TEXT)
RETURNS INTEGER AS $$
#variable_conflict use_variable
  DECLARE
    user_id BIGINT;
    other_param TEXT;
  BEGIN
    SELECT INTO user_id users.user_id FROM users WHERE users.username = username;
    RETURN user_id;
  END
$$ LANGUAGE PLPGSQL
;
CREATE FUNCTION

let's check:

t=# create table users (user_id int, username text);
CREATE TABLE
t=# insert into users values (1,'a');
INSERT 0 1
t=# select get_user_id('a');
 get_user_id
-------------
           1
(1 row)

Attention - this check has big sense, and if you disable it, you can get some very ugly and very invisible errors. This is pretty dangerous. Don't do it, please.

Don't use it, unless you see invisible errors or you hold EVERY line under control

Upvotes: 3

Pavel Stehule
Pavel Stehule

Reputation: 45950

Any local variable can be qualified by block label

create table foo(a integer);
insert into foo values(10);

do $$
<<mylabel>>
declare
  a int default 5;
  r record;
begin
  select foo.a into r
    from foo
    where foo.a = mylabel.a + 5;
  raise notice '%', r.a;
end;
$$;
NOTICE:  10
DO

Usually all possible conflict local variables has prefix - common prefix is _.

Upvotes: 8

Gordon Linoff
Gordon Linoff

Reputation: 1271211

I prefix parameters and variables so they are less likely to conflict with column names:

CREATE OR REPLACE FUNCTION get_user_id (in_username TEXT) 
RETURNS INTEGER AS $$
  DECLARE
    v_user_id BIGINT;
  BEGIN
    SELECT u.user_id  INTO v_user_id FROM users u WHERE u.user_id = in_user_id;

    RETURN v_user_id;
  END
$$ LANGUAGE PLPGSQL

Presumably, though, you want to compare user names:

    SELECT u.user_id  INTO v_user_id FROM users u WHERE u.username = in_username;

Upvotes: 7

Rafael Ara&#250;jo
Rafael Ara&#250;jo

Reputation: 201

It is not missing username filtering? I think you wanted something like:

SELECT users.user_id INTO user_id FROM users WHERE users.username = username;

Is that way, the value of users.user_id will be set in user_id variable after a username filtering.

Upvotes: 0

Related Questions