sql - 输入参数数量可变的函数

Question

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

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;

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

Answer 1

VARIADIC 甚至 polymorphic input types 等高级功能和动态 SQL 非常强大。本回答的最后一章提供了一个高级示例:

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

对于像您这样的简单案例，您可以只对函数参数使用默认值。这一切都取决于具体要求。
如果有问题的列都被定义为NOT NULL，这可能会更简单和更快:

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

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

了解 default values in the manual!

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

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

函数调用

三个选项:

1. Positional notation
   是简单的方法，但它只允许省略最右边的参数:

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

2. Named notation
   允许省略具有默认值的任何参数:

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

3. Mixed notation
   结合两者:

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

直到 Postgres 9.4，使用 := 代替 => 在通话中分配:

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

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