可变数量输入参数的函数

Question

可变数量输入参数的函数

sqlfunctionpostgresqlstored-proceduressql-update

8

我正在创建一个在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(<表中的所有列>)和一个单独的函数update_site(id integer, <要更新的varchar列>)，还是使用一种模式在一个函数中定义区别？哪个选项更有效率？是一个独特的函数还是为每个目的而不同的函数？

- anairinac

2个回答

4

以下是您需要了解的内容：

使用format函数动态构建SQL语句，其中包含%I和%L指示符，然后使用EXECUTE ... USING执行该语句；
使用VARIADIC参数接收变量数量的参数，但要注意所有参数必须是相同的数据类型。

- Craig Ringer

谢谢你的回答。我找到了关于EXECUTE格式的信息，并且可以实现我所要求的变量参数，但由于每次执行函数时都会重新计算EXECUTE，所以最终效率可能比普通函数低。我想知道是否可能，但对于我被要求的内容来说，这已经足够了。无论如何，谢谢。我一定会多读一些相关资料。看起来很有趣。 - anairinac

@kzz 是的，这是一个有用的工具，但要小心使用 EXECUTE ... USING 或安全格式参数，以避免通过数据库内动态SQL意外暴露SQL注入。 - Craig Ringer

太棒了的回答！！！ - ennth

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

像VARIADIC这样的高级特性，甚至多态输入类型和动态SQL都非常强大。本回答的最后一章提供了一个高级示例：

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

对于像您这样的简单情况，您可以为函数参数使用默认值。这完全取决于确切的要求。
如果所涉及的列都定义为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$;

阅读关于手册中的默认值！

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

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

函数调用

三个选项：

位置表示法
这是一种简单的方法，但它只允许省略最右边的参数：
```
SELECT update_site('foo', 'New York');  -- 没有电话
```

命名表示法
允许省略任何具有默认值的参数：

SELECT update_site(name => 'foo', _telephone => 123);  -- 没有城市

混合表示法
结合了两者：

SELECT update_site('foo', _telephone => 123);  -- 仍然没有城市

在 Postgres 9.4 及之前版本中，赋值操作使用 := 而不是 =>：

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

为了向后兼容性仍然适用于Postgres 15，但最好使用现代语法。