它可以引用 PL/pgSQL 变量或表列 [英] It could refer to either a PL/pgSQL variable or a table column

问题描述

我在 pgsql 中有一个函数

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

推荐答案

SQL 标识符和 PlpgSQL 变量之间存在冲突.没有干净，你想要什么.你写了一个谓词，它总是正确的.

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.

好用:

• 局部变量的前缀(通常是_")
• 嵌入式 SQL 中的限定名称 - 如 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;

对于这些单行函数，SQL 语言更好:

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;

这篇关于它可以引用 PL/pgSQL 变量或表列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！