postgresql PL/pgSQL 函数中的可选参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11519410/
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
Optional argument in PL/pgSQL function
提问by Homunculus Reticulli
I am trying to write a PL/pgSQL function with optional arguments. It performs a query based on a filtered set of records (if specified), otherwise performs a query on the entire data set in a table.
我正在尝试编写一个带有可选参数的 PL/pgSQL 函数。它基于过滤的记录集（如果指定）执行查询，否则对表中的整个数据集执行查询。
For example (PSEUDO CODE):
例如（伪代码）：
CREATE OR REPLACE FUNCTION foofunc(param1 integer, param2 date, param2 date, optional_list_of_ids=[]) RETURNS SETOF RECORD AS $$
IF len(optional_list_of_ids) > 0 THEN
RETURN QUERY (SELECT * from foobar where f1=param1 AND f2=param2 AND id in optional_list_of_ids);
ELSE
RETURN QUERY (SELECT * from foobar where f1=param1 AND f2=param2);
ENDIF
$$ LANGUAGE SQL;
What would be the correct way to implement this function?
实现此功能的正确方法是什么？
As an aside, I would like to know how I could call such a function in another outer function. This is how I would do it - is it correct, or is there a better way?
顺便说一句，我想知道如何在另一个外部函数中调用这样的函数。这就是我要做的 - 是正确的，还是有更好的方法？
CREATE FUNCTION foofuncwrapper(param1 integer, param2 date, param2 date) RETURNS SETOF RECORD AS $$
BEGIN
CREATE TABLE ids AS SELECT id from foobar where id < 100;
RETURN QUERY (SELECT * FROM foofunc(param1, param2, ids));
END
$$ LANGUAGE SQL
回答by Erwin Brandstetter
Since PostgreSQL 8.4 (which you seem to be running), there are default values for function parameters. If you put your parameter last and provide a default, you can simply omit it from the call:
从 PostgreSQL 8.4（您似乎正在运行）开始，函数 parameters有默认值。如果你把你的参数放在最后并提供一个默认值，你可以简单地从调用中省略它：
CREATE OR REPLACE FUNCTION foofunc(_param1 integer
, _param2 date
, _ids int[] DEFAULT '{}')
RETURNS SETOF foobar AS -- declare return type!
$func$
BEGIN -- required for plpgsql
IF _ids <> '{}'::int[] THEN -- exclude empty array and NULL
RETURN QUERY
SELECT *
FROM foobar
WHERE f1 = _param1
AND f2 = _param2
AND id = ANY(_ids); -- "IN" is not proper syntax for arrays
ELSE
RETURN QUERY
SELECT *
FROM foobar
WHERE f1 = _param1
AND f2 = _param2;
END IF;
END -- required for plpgsql
$func$ LANGUAGE plpgsql;
Major points:
要点：
The keyword
DEFAULTis used to declare parameter defaults. Short alternative:=.I removed the redundant
param1from the messy example.Since you return
SELECT * FROM foobar, declare the return type asRETURNS SETOF foobarinstead ofRETURNS SETOF record. The latter form with anonymous records is very unwieldy, you'd have to provide a column definition list with every call.I use an array of integer (
int[]) as function parameter. Adapted theIFexpression and theWHEREclause accordingly.IFstatements are not available in plain SQL. Has to beLANGUAGE plpgsqlfor that.
关键字
DEFAULT用于声明参数默认值。简短的替代方案：=.我
param1从凌乱的例子中删除了多余的部分。由于您返回
SELECT * FROM foobar，请将返回类型声明为RETURNS SETOF foobar而不是RETURNS SETOF record。带有匿名记录的后一种形式非常笨拙，您必须在每次调用时提供一个列定义列表。我使用整数 (
int[])数组作为函数参数。相应地修改了IF表达式和WHERE子句。IF语句在普通 SQL 中不可用。必须LANGUAGE plpgsql为此。
Call with or without _ids:
有或没有呼叫_ids：
SELECT * FROM foofunc(1, '2012-1-1'::date);
Effectively the same:
实际上相同：
SELECT * FROM foofunc(1, '2012-1-1'::date, '{}'::int[]);
You have to make sure the call is unambiguous. If you have another function of the same name and two parameters, Postgres might not know which to pick. Explicit casting (like I demonstrate) narrows it down. Else, untyped string literals work, too, but being explicit never hurts.
你必须确保调用是明确的。如果您有另一个同名函数和两个参数，Postgres 可能不知道该选择哪个。显式转换（就像我演示的那样）缩小了范围。否则，无类型的字符串文字也可以，但显式永远不会有什么坏处。
Call from within another function:
从另一个函数内部调用：
CREATE FUNCTION foofuncwrapper(_param1 integer, _param2 date)
RETURNS SETOF foobar AS
$func$
DECLARE
_ids int[] := '{1,2,3}';
BEGIN
-- irrelevant stuff
RETURN QUERY
SELECT * FROM foofunc(_param1, _param2, _ids);
END
$func$ LANGUAGE plgpsql;
回答by Craig Ringer
Elaborating on Frank's answer on this thread:
详细说明Frank对此线程的回答：
The VARIADICagument doesn't have to be the only argument, only the last one.
该VARIADICagument不必是唯一的说法，只有最后一个。
You can use VARIADICfor functions that may take zero variadic arguments, it's just a little fiddlier in that it requires a different calling style for zero args. You can provide a wrapper function to hide the ugliness. Given an initial varardic function definition like:
您可以使用VARIADIC可能采用零可变参数的函数，它只是有点麻烦，因为它需要不同的零参数调用风格。您可以提供一个包装函数来隐藏丑陋。给定一个初始 varardic 函数定义，如：
CREATE OR REPLACE FUNCTION foofunc(param1 integer, param2 date, param2 date, optional_list_of_ids VARIADIC integer[]) RETURNS SETOF RECORD AS $$
....
$$ language sql;
For zero args use a wrapper like:
对于零参数，请使用如下包装器：
CREATE OR REPLACE FUNCTION foofunc(integer, date, date) RETURNS SETOF RECORD AS $body$
SELECT foofunc(,,,VARIADIC ARRAY[]::integer[]);
$body$ LANGUAGE 'sql';
or just call the main func with an empty array like VARIADIC '{}'::integer[]directly. The wrapper is ugly, but it's contained ugliness, so I'd recommend using a wrapper.
或者VARIADIC '{}'::integer[]直接使用空数组调用 main func 。包装器很丑，但它包含丑陋，所以我建议使用包装器。
Direct calls can be made in variadic form:
可以以可变参数形式进行直接调用：
SELECT foofunc(1,'2011-01-01','2011-01-01', 1, 2, 3, 4);
... or array call form with array ctor:
...或使用数组构造函数的数组调用形式：
SELECT foofunc(1,'2011-01-01','2011-01-01', VARIADIC ARRAY[1,2,3,4]);
... or array text literal form:
...或数组文本文字形式：
SELECT foofunc(1,'2011-01-01','2011-01-01', VARIADIC '{1,2,3,4}'::int[]);
The latter two forms work with empty arrays.
后两种形式适用于空数组。
回答by Frank Heikens
You mean SQL Functions with Variable Numbers of Arguments? If so, use VARIADIC.
您的意思是具有可变数量参数的 SQL 函数？如果是这样，请使用 VARIADIC。
