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

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;

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.