It could refer to either a PL/pgSQL variable or a table column
Solution 1
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;
Solution 2
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.
Comments
-
mban94 over 3 years
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