postgresql - PL/pgSQL 函数中的可选参数

Question

我正在尝试编写一个带有可选参数的 PL/pgSQL 函数。它根据过滤后的记录集(如果指定)执行查询，否则对表中的整个数据集执行查询。

例如(伪代码):

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;

实现此功能的正确方法是什么？

顺便说一句，我想知道如何在另一个外部函数中调用这样的函数。这就是我的做法 - 它是正确的，还是有更好的方法？

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

Answer 1

自 PostgreSQL 8.4(您似乎正在运行)以来，有 default values for function parameters .如果将参数放在最后并提供默认值，则可以简单地从调用中省略它:

CREATE OR REPLACE FUNCTION foofunc(_param1 integer
                                 , _param2 date
                                 , _ids    int[] DEFAULT '{}')
  RETURNS SETOF foobar         -- declare return type!
  LANGUAGE plpgsql AS
$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$;

要点:

• 关键字 DEFAULT 用于声明参数默认值。简称:=。

• 我从凌乱的示例中删除了多余的 param1。

• 由于您返回了 SELECT * FROM foobar，因此将返回类型声明为 RETURNS SETOF foobar 而不是 RETURNS SETOF record。带有匿名记录的后一种形式非常笨拙，您必须在每次调用时提供列定义列表。

• 我使用整数数组 (int[]) 作为函数参数。相应地调整了 IF 表达式和 WHERE 子句。

• IF 语句在纯 SQL 中不可用。必须是 LANGUAGE plpgsql。

使用或不使用 _ids 调用:

SELECT * FROM foofunc(1, '2012-1-1'::date);

效果相同:

SELECT * FROM foofunc(1, '2012-1-1'::date, '{}'::int[]);

您必须确保调用是明确的。如果您有另一个同名函数和两个参数，Postgres 可能不知道该选择哪个。显式转换(就像我演示的那样)缩小了范围。此外，无类型字符串字面量也可以工作，但明确无误。

从另一个函数中调用:

CREATE FUNCTION foofuncwrapper(_param1 integer, _param2 date)
  RETURNS SETOF foobar
  LANGUAGE plgpsql AS
$func$
DECLARE
   _ids int[] := '{1,2,3}';
BEGIN
   -- whatever

   RETURN QUERY
   SELECT * FROM foofunc(_param1, _param2, _ids);
END
$func$;