Created
February 8, 2017 20:29
-
-
Save jasonaowen/278be19ac693e85b3bdda54540db4e56 to your computer and use it in GitHub Desktop.
How do you use variable names that shadow column names?
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
> 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE foobar(bar integer); | |
CREATE FUNCTION foo(OUT bar integer) AS $$ | |
BEGIN | |
SELECT INTO bar bar | |
FROM foobar | |
LIMIT 1; | |
END; | |
$$ LANGUAGE plpgsql; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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]:
(Also these are all awful variable/table/function names)