如何在Postgres函数中使用EXECUTE FORMAT ... USING

Question

CREATE OR REPLACE FUNCTION dummytest_insert_trigger()
   RETURNS trigger AS
$BODY$
DECLARE
   v_partition_name    VARCHAR(32);
BEGIN
   IF NEW.datetime IS NOT NULL THEN
      v_partition_name := 'dummyTest';            
      EXECUTE format('INSERT INTO %I VALUES ($1,$2)',v_partition_name)using NEW.id,NEW.datetime;            
   END IF;                  
   RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;

我正在尝试使用以下方法进行插入：

INSERT INTO dummytest VALUES(1,'2013-01-01 00:00:00+05:30');

但是它显示错误为：

ERROR: function format(unknown) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Context: PL/pgSQL function "dummytest_insert_trigger" line 8 at EXECUTE statement

我无法获取错误。

- vg123

---

你正在使用哪个版本的PostgreSQL？据我所知，format函数是在9.1版中添加的。 - mu is too short

Answer 1

在Postgres 9.0或更高版本中，您的函数可能如下所示：

CREATE OR REPLACE FUNCTION dummytest_insert_trigger()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
DECLARE
   v_partition_name text := quote_ident('dummyTest');  -- assign at declaration
BEGIN
   IF NEW.datetime IS NOT NULL THEN
      EXECUTE 'INSERT INTO ' || v_partition_name || ' VALUES ($1,$2)'
      USING NEW.id, NEW.datetime;              
   END IF;                    

   RETURN NULL;  -- you sure about this?
END
$func$;

关于 RETURN NULL:

• 如何在PostgreSQL的BEFORE TRIGGER中忽略结果？

我建议不要使用混合大小写标识符。使用format(... %I ...)或quote_ident()，你会得到一个名为"dummyTest"的表，它在其余的存在中都需要加上双引号。相关链接：

• PostgreSQL的列名区分大小写吗？

改用小写字母：

quote_ident('dummytest')

只要有静态表名，实际上使用动态SQL与EXECUTE没有任何意义。但这可能只是一个简化的例子吧？

顺便说一下，在Postgres 10或更高版本中，这种触发器已经过时了，可以使用声明式分区。

- Erwin Brandstetter

---

是的，这只是一个简化的示例。实际上我正在使用数据库分区来动态生成表名，但是对于更简单的部分仍然不起作用。我在Postgres 9.1上使用了所有小写字母的dummytest进行测试，并且出现以下错误：ERROR:栈深度限制已超过 HINT:增加配置参数“max_stack_depth”（当前为2048kB），确保平台的堆栈深度限制足够。 CONTEXT: SQL语句“INSERT INTO dummytest VALUES（$1，$2）” PL / pgSQL函数“dummytest_insert_trigger”的第7行处于执行状态。 - vg123

@mona：听起来你的触发器可能会创建一个无限循环。避免这种情况是你的责任。 - Erwin Brandstetter

@ Erwin Brandstetter ：是的，那是我的错误。谢谢您的解决方案，它起作用了。 :) :) - vg123

这太完美了。“只要你有一个静态表名，就真的没有必要使用EXECUTE和动态SQL。” - Ankur Srivastava

Answer 2

您需要进行显式类型转换为text:

EXECUTE format('INSERT INTO %I VALUES ($1,$2)'::text ,v_partition_name) using NEW.id,NEW.datetime;

- mys

---

是的，我明白了问题。这是由于版本引起的。我使用的是9.0版本。谢谢。 - vg123

@mona：一旦您升级：不需要将类型转换为“text”，因为引用字面量的默认类型就是“text”。 - Erwin Brandstetter