It could refer to either a PL/pgSQL variable or a table column

Question

I have a function in pgsql

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

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 — , Feb 09 '14 at 17:20
@a_horse_with_no_name, your comment should be an answer and not a comment. Pls post it as answer. — Rahul, Feb 09 '14 at 17:27
Does this answer your question? [Postgresql column reference "id" is ambiguous](https://stackoverflow.com/questions/9821121/postgresql-column-reference-id-is-ambiguous) — TylerH, Oct 20 '22 at 13:38

Ezequiel Tolnay · Accepted Answer · 2017-02-08T23:42:40.450

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.

score 33 · Answer 2 · answered Feb 09 '14 at 17:29

33

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;

answered Feb 09 '14 at 17:29

Pavel Stehule

42,331
5
91
94

6

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). – Mureinik Feb 09 '14 at 17:30
Hungarian convention has no sense here. But good naming is important. Smart naming for PK and FK can simplify writing and reading queries. – Pavel Stehule Feb 09 '14 at 18:04
1

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. – filiprem Mar 04 '23 at 23:15

It could refer to either a PL/pgSQL variable or a table column

2 Answers2

Linked