postgresql 它可以引用 PL/pgSQL 变量或表列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21662295/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
It could refer to either a PL/pgSQL variable or a table column
提问by mban94
I have a function in pgsql
我在 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 =
);
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 =
^
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 =
)
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
采纳答案by Pavel Stehule
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 标识符和 PlpgSQL 变量之间存在冲突。没有干净，你想要什么。你写了一个谓词，它总是正确的。
Good to use:
好用：
- prefix (usually "_") for local variables
- qualified names in embedded SQL - like table_name.column_name
- 局部变量的前缀（通常是“_”）
- 嵌入式 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;
For these one line functions is SQL language better:
对于这些单行函数，SQL 语言更好：
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 = ;
$$ LANGUAGE sql;
回答by Ziggy Crueltyfree Zeitgeister
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:
在这种情况下，代码足够简单明了，有时在函数文本的开头依赖这些特殊的 plpgsql 命令之一会很有用：
#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 =
);
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_bsis the output variable name or one of core.date_conversion's columns. For problems like these, the command #variable_conflict use_columncan really help.
上面的函数将失败，因为编译器无法确定date_in_bs是输出变量名称还是core.date_conversion的列之一。对于此类问题，该命令#variable_conflict use_column确实很有帮助。
