Skip to content

Instantly share code, notes, and snippets.

@jasonaowen
Created February 8, 2017 20:29
Show Gist options
  • Save jasonaowen/278be19ac693e85b3bdda54540db4e56 to your computer and use it in GitHub Desktop.
Save jasonaowen/278be19ac693e85b3bdda54540db4e56 to your computer and use it in GitHub Desktop.
How do you use variable names that shadow column names?
> select foo()
column reference "bar" is ambiguous
LINE 1: SELECT bar FROM foobar LIMIT 1
^
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
QUERY: SELECT bar FROM foobar LIMIT 1
CONTEXT: PL/pgSQL function foo() line 1 at SQL statement
CREATE TABLE foobar(bar integer);
CREATE FUNCTION foo(OUT bar integer) AS $$
BEGIN
SELECT INTO bar bar
FROM foobar
LIMIT 1;
END;
$$ LANGUAGE plpgsql;
@jasonaowen
Copy link
Author

Aha, more information in https://www.postgresql.org/docs/current/static/plpgsql-implementation.html , including how to label declaration blocks to disambiguate. To disambiguate parameters, you can prefix them with the name of the function[1]:

SELECT INTO bar foobar.bar 
  FROM foobar
  WHERE foobar.bar = foo.bar
  LIMIT 1; 

(Also these are all awful variable/table/function names)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment