PostgreSQL - Function with local variables - Column reference is ambiguous

Question

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.

Answer 1

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 _.