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

mban94 picture mban94 · Feb 9, 2014 · Viewed 16.5k times · Source

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

Answer

Ezequiel Tolnay picture Ezequiel Tolnay · Feb 9, 2017

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.