50

I have a function in plpgsql:

CREATE OR REPLACE FUNCTION core.date_bs_from_ad(date_in_ad date) RETURNS character varying AS $$ BEGIN RETURN (SELECT date_in_bs FROM core.date_conversion WHERE date_in_ad = $1); END $$ LANGUAGE plpgsql;

It is created with no errors, but when I use this function it through following error:

ERROR: column reference "date_in_ad" is ambiguous LINE 3: WHERE date_in_ad = $1 ^ DETAIL: It could refer to either a PL/pgSQL variable or a table column. QUERY: SELECT ( SELECT MAX(date_in_bs) FROM core.date_conversion WHERE date_in_ad = $1 ) CONTEXT: PL/pgSQL function core.date_bs_from_ad(date) line 3 at RETURN ********** Error ********** ERROR: column reference "date_in_ad" is ambiguous SQL state: 42702 Detail: It could refer to either a PL/pgSQL variable or a table column. Context: PL/pgSQL function core.date_bs_from_ad(date) line 3 at RETURN
Improve this question
3
  • 5
    The error messages says it all: you have a column and a parameter with the same name. You need to change the name of the parameter to avoid ambiguity Commented Feb 9, 2014 at 17:20
  • 1
    @a_horse_with_no_name, your comment should be an answer and not a comment. Pls post it as answer. Commented Feb 9, 2014 at 17:27
  • Does this answer your question? Postgresql column reference "id" is ambiguous Commented Oct 20, 2022 at 13:38

4 Answers 4

Reset to default
75

In cases like these, where the code is simple straightforward enough, sometimes it is useful to rely on one of these special plpgsql commands at the start of the function text:

#variable_conflict error #variable_conflict use_variable #variable_conflict use_column

In this case, it would be used as follows:

CREATE OR REPLACE FUNCTION core.date_bs_from_ad(date_in_ad date) RETURNS character varying AS $$ #variable_conflict use_column BEGIN RETURN( SELECT date_in_bs FROM core.date_conversion WHERE date_in_ad = $1 ); END $$

This is especially useful for cases when the clash is not with the parameters, but rather with the output column names, such as this:

CREATE OR REPLACE FUNCTION core.date_bs_from_ad(p_date_in_ad date) RETURNS TABLE (date_in_bs character varying) AS $$ BEGIN RETURN QUERY SELECT date_in_bs FROM core.date_conversion WHERE date_in_ad = p_date_in_ad; END; $$

The function above will fail because it the compiler cannot decide if date_in_bs is the output variable name or one of core.date_conversion's columns. For problems like these, the command #variable_conflict use_column can really help.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

36

There is a collision between SQL identifier and PlpgSQL variable. There are no clean, what do you want. You wrote a predicate, that is TRUE always.

Good to use:

  • prefix (usually "_") for local variables
  • qualified names in embedded SQL - like table_name.column_name

so both techniques (only one is necessary)

CREATE OR REPLACE FUNCTION core.date_bs_from_ad(_date_in_ad date) RETURNS character varying AS $$ BEGIN RETURN SELECT dc.date_in_bs FROM core.date_conversion dc WHERE dc.date_in_ad = _date_in_ad; END $$ LANGUAGE plpgsql;

For these one line functions is SQL language better:

CREATE OR REPLACE FUNCTION core.date_bs_from_ad(_date_in_ad date) RETURNS character varying AS $$ SELECT dc.date_in_bs FROM core.date_conversion dc WHERE dc.date_in_ad = $1; $$ LANGUAGE sql;
Improve this answer

3 Comments

Not a big fan of the _ prefix myself - it's a bit hard to notice, and can be confusing when reading large amounts of code. I prefer a more noticeable prefix, like p_. (Regardless, the answer is correct, of course).
Hungarian convention has no sense here. But good naming is important. Smart naming for PK and FK can simplify writing and reading queries.
A clean API does not munge parameter names, that's why this practice of renaming params to avoid conflicts is in fact a bad practice. It's better to use qualified names in pl/pgsql code.
2

Another option not mentioned in the other answers is prefixing the function name core.date_bs_from_ad to the column name date_in_ad so that you get core.date_bs_from_ad.date_in_ad.

The specifics of this question make it clear that using one of the other answers is the correct choice for the individual who asked the question but this is a valid and missing answer based on the title of the question.

I was brought here by google and didn't find this option mentioned and in my case prefixing the function name was what I needed.

Improve this answer

Comments

0

I created test table, then inserted the row whose num is 0 into it as shown below:

CREATE TABLE test ( num INTEGER ); INSERT INTO test (num) VALUES (0);

Then, I ran the DO statement below to increment num by 1:

DO $$DECLARE num INTEGER := 2; BEGIN UPDATE test SET num = num + 1; END $$;

But I got the same error below:

ERROR: column reference "num" is ambiguous LINE 1: UPDATE test SET num = num + 1 ^ DETAIL: It could refer to either a PL/pgSQL variable or a table column. QUERY: UPDATE test SET num = num + 1 CONTEXT: PL/pgSQL function inline_code_block line 4 at SQL statement

So, I removed DECLARE clause as shown below, then I could run the DO statement without error:

DO $$/*DECLARE num INTEGER := 2;*/ BEGIN UPDATE test SET num = num + 1; END $$;
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.