PL/pgSQL函数中的可选参数 [英] Optional argument in PL/pgSQL function

问题描述

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

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.

例如(伪代码):

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

推荐答案

自PostgreSQL 8.4(您似乎正在运行)以来，就有

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:

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;

要点:

• 关键字DEFAULT用于声明参数默认值.简短替代:=.

• The keyword DEFAULT is used to declare parameter defaults. Short alternative: =.

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

I removed the redundant param1 from the messy example.

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

Since you return SELECT * FROM foobar, declare the return type as RETURNS SETOF foobar instead of RETURNS SETOF record. The latter form with anonymous records is very unwieldy, you'd have to provide a column definition list with every call.

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

I use an array of integer (int[]) as function parameter. Adapted the IF expression and the WHERE clause accordingly.

IF语句在普通SQL中不可用.为此必须为LANGUAGE plpgsql.

IF statements are not available in plain SQL. Has to be LANGUAGE plpgsql for that.

有或没有_ids的呼叫:

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

实际上是相同的:

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

您必须确保通话是明确的.如果您具有另一个具有相同名称和两个参数的函数，则Postgres可能不知道该选择哪个函数.显式强制转换(如我所演示的)将其范围缩小.否则，无类型的字符串文字也可以使用，但是显式的使用永远不会有伤害.

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.

从另一个函数中调用:

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;

这篇关于PL/pgSQL函数中的可选参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！