postgresql 具有可变数量输入参数的函数

Functions with variable number of input parameters

提问by anairinac

I'm creating a stored procedure (function) in a PostgreSQL DB, which updates a table depending on its input. In order to create a variable number of parameter function, I'm creating an extra input parameter called mode, which I use to control which parameters I use on the update query.

我正在 PostgreSQL 数据库中创建一个存储过程（函数），它根据输入更新表。为了创建可变数量的参数函数，我创建了一个名为 mode 的额外输入参数，我用它来控制我在更新查询中使用的参数。

CREATE OR REPLACE FUNCTION update_site(
    mode integer,
    name character varying,
    city character varying,
    telephone integer,
)
RETURNS integer AS
$$
BEGIN
IF mode = 0 THEN
BEGIN
    UPDATE "Sites" SET 
    ("City","Telephone") = (city,telephone)
    WHERE "SiteName" = name;
    RETURN 1;
    EXCEPTION WHEN others THEN
    RAISE NOTICE 'Error on site update: %, %',SQLERRM,SQLSTATE;
    RETURN 0;
END;
ELSIF mode = 1 THEN
BEGIN
    UPDATE "Sites" SET "City" = city
    WHERE "SiteName" = name;
    RETURN 1;
    EXCEPTION WHEN others THEN
    RAISE NOTICE 'Error on site update: %, %',SQLERRM,SQLSTATE;
    RETURN 0;
END;
    ELSIF mode = 2 THEN
BEGIN
    UPDATE "Sites" SET "Telephone" = telephone
    WHERE "SiteName" = name;
    RETURN 1;
    EXCEPTION WHEN others THEN
    RAISE NOTICE 'Error on site update: %, %',SQLERRM,SQLSTATE;
    RETURN 0;
END;
    ELSE
            RAISE NOTICE 'Error on site update: %, %',SQLERRM,SQLSTATE;
    RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql;

What would be best? To create a function update_site(<all the columns of table>)and a separate function update_site(id integer, <varchar column to update>), or use the mode in one function to define the difference? Which option is more efficient? One unique function or different ones for each purpose?

什么是最好的？创建一个函数update_site(<all the columns of table>)和一个单独的函数update_site(id integer, <varchar column to update>)，还是在一个函数中使用模式来定义区别？哪个选项更有效？一种独特的功能还是针对每种用途的不同功能？

回答by Erwin Brandstetter

Advanced features like VARIADICor even polymorphic input typesand dynamic SQL are very powerful. The last chapter in this answer provides an advanced example:

诸如VARIADIC甚至多态输入类型和动态 SQL 之类的高级功能非常强大。本答案的最后一章提供了一个高级示例：

• Refactor a PL/pgSQL function to return the output of various SELECT queries

• 重构一个 PL/pgSQL 函数以返回各种 SELECT 查询的输出

But for a simple case like yours, you can just use default valuesfor function parameters. It all depends on exact requirements.
Ifthe columns in question are all defined NOT NULL, this would probably be simpler and faster:

但是对于像您这样的简单情况，您可以只使用函数参数的默认值。这一切都取决于确切的要求。
如果有问题的列都已定义NOT NULL，这可能会更简单、更快：

CREATE OR REPLACE FUNCTION update_site(_name      text    -- always required
                                     , _city      text    DEFAULT NULL
                                     , _telephone integer DEFAULT NULL)
  RETURNS integer AS
$func$
BEGIN
   IF _city IS NULL AND _telephone IS NULL THEN
      RAISE WARNING 'At least one value to update required!';
      RETURN;  -- nothing to update
   END IF;

   UPDATE "Sites"
   SET    "City"      = COALESCE(_city, "City")
        , "Telephone" = COALESCE(_telephone, "Telephone")
   WHERE  "SiteName"  = _name;
END
$func$  LANGUAGE plpgsql;

Read about default values in the manual!

阅读手册中的默认值！

To avoid naming conflicts between parameters and column names I make it a habit to prefix input parameters with _. That's a matter of taste and style.

为了避免参数和列名之间的命名冲突，我习惯于在输入参数前加上_. 这是品味和风格的问题。

• The first parameter namehas no default, since it is required at all times.
• Other parameters can be omitted.
• At least one is required, or a WARNINGis raised and nothing else happens.
• The UPDATEwill only change columns for given parameters.
• Can easily be expanded for Nparameters.

• 第一个参数name没有默认值，因为它始终是必需的。
• 其他参数可以省略。
• 至少需要一个，或者 aWARNING被引发并且没有其他任何事情发生。
• 将UPDATE只会更改列给定参数。
• 可以轻松扩展N 个参数。

Function call

函数调用

Since Postgres 9.5:

从Postgres 9.5 开始：

The simple way is with positional notationfor parameters. This only allows to omit the rightmost parameter(s):

简单的方法是使用参数的位置符号。这只允许省略最右边的参数：

SELECT update_site('foo', 'New York');  -- no telephone

Named notationallows to omit anyparameter that has a default value:

命名符号允许省略任何具有默认值的参数：

SELECT update_site(name => 'foo', _telephone => 123);  -- no city

Both can be combined in mixed notation:

两者都可以混合表示法组合：

SELECT update_site('foo', _telephone => 123);  -- still no city

In Postgres 9.4or older, :=was used for assignment in the call:

在Postgres 9.4或更早版本中，:=用于调用中的赋值：

SELECT update_site(name := 'foo', _telephone := 123);
SELECT update_site('foo', _telephone := 123);

Still valid in Postgres 12 for backward compatibility, but rather use the modern notation.

为了向后兼容，在 Postgres 12 中仍然有效，而是使用现代表示法。

回答by Craig Ringer

There are a few things you'll want to look into:

您需要了解以下几点：

• Dynamically building the SQL using the formatfunction and its %Iand %Lspecifiers, then executing it with EXECUTE ... USING; and

• Using VARIADICparameters to take variable numbers of arguments to the function, with the caveat that they must all be the same data type.

• 动态使用生成SQLformat函数及其%I与%L符，然后执行它EXECUTE ... USING; 和

• 使用VARIADIC参数将可变数量的参数传递给函数，但需要注意的是它们都必须是相同的数据类型。