问题描述
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;
ALTER FUNCTION dummytest_insert_trigger()
OWNER TO postgres;
我正在尝试使用插入 dummyTest 值(1,'2013-01-01 00:00:00+05:30');
I'm trying to insert usinginsert 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
我无法得到错误.
推荐答案
您的函数在 Postgres 9.0 或更高版本中可能如下所示:
Your function could look like this in Postgres 9.0 or later:
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 ($1,$2)'
USING NEW.id, NEW.datetime;
END IF;
RETURN NULL; -- You sure about this?
END
$func$ LANGUAGE plpgsql;
关于RETURN NULL
:
我建议不要使用混合大小写标识符.使用 format( .. %I ..)
或 quote_ident()
,您会得到一个名为 "dummyTest"
的表,您可以使用它在其存在的其余部分中,我必须双引号.相关:
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:
改用小写:
quote_ident('dummytest')
只要您有一个静态表名,就没有必要将动态 SQL 与 EXECUTE
一起使用.但这可能只是简化的示例?
There is really no point in using dynamic SQL with EXECUTE
as long as you have a static table name. But that's probably just the simplified example?
这篇关于如何在 postgres 函数中使用 EXECUTE FORMAT ... USING的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!