postgresql - 它可以引用 PL/pgSQL 变量或表列

Question

我在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;

它创建时没有错误，但是当我使用这个函数时却出现了以下错误:

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

在这种情况下，代码足够简单明了，有时在函数文本的开头依赖这些特殊的 plpgsql 命令之一会很有用:

#variable_conflict error
#variable_conflict use_variable
#variable_conflict use_column

在这种情况下，它将按如下方式使用:

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
$$

这对于冲突不是与参数有关，而是与输出列名称有关的情况特别有用，例如:

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;
$$

上面的函数将失败，因为编译器无法确定 date_in_bs 是输出变量名还是 core.date_conversion 的列之一。对于此类问题，命令 #variable_conflict use_column 真的很有帮助。