postgresql 如何在 postgres 函数中使用 EXECUTE FORMAT ... USING
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14065271/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use EXECUTE FORMAT ... USING in postgres function
提问by vg123
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 (,)',v_partition_name)using NEW.id,NEW.datetime;
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION dummytest_insert_trigger()
OWNER TO postgres;
I'm trying to insert using insert into dummyTest values(1,'2013-01-01 00:00:00+05:30');
我正在尝试使用 insert into dummyTest values(1,'2013-01-01 00:00:00+05:30');
But it's showing error as
但它显示的错误为
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
I'm unable get the error.
我无法得到错误。
回答by Erwin Brandstetter
Your function could look like this in Postgres 9.0 or later:
您的函数在 Postgres 9.0 或更高版本中可能如下所示：
CREATE OR REPLACE FUNCTION dummytest_insert_trigger()
RETURNS trigger 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 (,)'
USING NEW.id, NEW.datetime;
END IF;
RETURN NULL; -- You sure about this?
END
$func$ LANGUAGE plpgsql;
About RETURN NULL:
关于RETURN NULL：
I would advice not to use mixed case identifiers. With format( .. %I ..)or quote_ident(), you'd get a table named "dummyTest", which you'll have to double quote for the rest of its existence. Related:
我建议不要使用大小写混合的标识符。使用format( .. %I ..)or quote_ident()，你会得到一个名为 的表"dummyTest"，你必须在它存在的其余部分用双引号引起来。有关的：
Use lower case instead:
改用小写：
quote_ident('dummytest')
There is really no point in using dynamic SQL with EXECUTEas long as you have a static table name. But that's probably just the simplified example?
EXECUTE只要您有一个静态表名，就没有必要使用动态 SQL 。但这可能只是简化的例子？
回答by mys
You need eplxicit cast to text:
您需要 eplxicit 强制转换为text：
EXECUTE format('INSERT INTO %I VALUES (,)'::text ,v_partition_name) using NEW.id,NEW.datetime;
