如何在 Postgres sql 函数中引用命名参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9771546/
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
How do I reference named parameters in Postgres sql functions?
提问by Nick Vaccaro
Postgres noobie here.
Postgres noobie 在这里。
I'm trying to convert a SQL Server stored proc into a Postgres function. Currently cannot figure out how to turn this SQL line into Postgres.
我正在尝试将 SQL Server 存储过程转换为 Postgres 函数。目前无法弄清楚如何将此 SQL 行转换为 Postgres。
SQL Server:
SQL 服务器：
input: @name = null
SELECT *
FROM table
WHERE name = ISNULL(@name, name)
Postgres:
Postgres：
input: n = null
SELECT *
FROM table
WHERE name = COALESCE(n, name)
I'm getting the error "column n does not exist." How do I reference parameters in select statements in Postgres functions?
我收到错误“第 n 列不存在”。如何在 Postgres 函数的 select 语句中引用参数？
UPDATE:
更新：
Definition of Postgres function
Postgres 函数的定义
CREATE OR REPLACE FUNCTION fn_name (n VARCHAR(32) = NULL, name OUT varchar(32), description OUT varchar(64))
RETURNS setof record
AS
$$
SELECT u.name
, u.description
FROM table_a u
WHERE u.name = COALESCE(n, u.name);
$$
LANGUAGE sql;
回答by dbenhur
REVISED: As pointed out in comments, this answer was accurate when written in early 2012, but named parameters have been supported since v9.2, released late 2012.
修订：正如评论中所指出的，这个答案在 2012 年初编写时是准确的，但自 2012 年末发布的 v9.2 以来就支持命名参数。
Parameter names are merely decoration when your function is in language SQL. You can use the parameters by name in stored procedures defined as language plpgsql.
当您的函数使用 SQL 语言时，参数名称只是装饰。您可以在定义为 的存储过程中按名称使用参数language plpgsql。
Consequently, you must refer to the function args using $X where X is the ordinal position of the function's argument list (starting with 1).
因此，您必须使用 $X 引用函数 args，其中 X 是函数参数列表的序数位置（从 1 开始）。
CREATE OR REPLACE FUNCTION fn_name (
n VARCHAR(32) = NULL,
OUT name varchar(32),
OUT description varchar(64) )
RETURNS setof record
AS
$$
SELECT u.name
, u.description
FROM table_a u
WHERE u.name = COALESCE(, u.name);
$$
LANGUAGE sql;
回答by a_horse_with_no_name
You cannot use named parameters in a function that is defined with language=SQL.
不能在使用 language=SQL 定义的函数中使用命名参数。
You need to use the the placeholder $1.
您需要使用占位符 $1。
CREATE OR REPLACE FUNCTION fn_name (n VARCHAR(32) = NULL, name OUT varchar(32), description OUT varchar(64))
RETURNS setof record
AS
$$
SELECT u.name
, u.description
FROM table_a u
WHERE u.name = COALESCE(, u.name);
$$
LANGUAGE sql;
This behaviour is documented in the manual: http://www.postgresql.org/docs/9.0/static/xfunc-sql.html
此行为记录在手册中：http: //www.postgresql.org/docs/9.0/static/xfunc-sql.html
So far as the SQL function itself is concerned, these names are just decoration; you must still refer to the parameters as $1, $2, etc within the function body
就SQL函数本身而言，这些名称只是装饰；您仍然必须在函数体内将参数称为 $1、$2 等
Edit
编辑
Since version 9.2 it is possible to use named parameters with (plain) SQL function
http://www.postgresql.org/docs/9.2/static/xfunc-sql.html#XFUNC-SQL-FUNCTION-ARGUMENTS
从 9.2 版开始，可以将命名参数与（纯）SQL 函数一起使用
http://www.postgresql.org/docs/9.2/static/xfunc-sql.html#XFUNC-SQL-FUNCTION-ARGUMENTS
