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 

Answer 1

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.

Answer 2

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;