如何使用EXECUTE FORMAT ...在postgres函数中使用 [英] How to use EXECUTE FORMAT ... USING in postgres function
问题描述
CREATE OR REPLACE FUNCTION dummytest_insert_trigger()
RETURNS触发器AS
$ BODY $
DECLARE
v_partition_name VARCHAR(32);
BEGIN
IF NEW.datetime不为NULL THEN
v_partition_name:='dummyTest';
EXECUTE格式('INSERT INTO%I VALUES($ 1,$ 2)',v_partition_name)使用NEW.id,NEW.datetime;
END IF;
RETURN NULL;
END;
$ BODY $
语言plpgsql VOLATILE
COST 100;
ALTER FUNCTION dummytest_insert_trigger()
OWNER TO postgres;
我试图用
插入到dummyTest值插入(1,'2013- 01-01 00:00:00 + 05:30');
但显示错误为
错误:函数格式(未知)不存在
SQL状态:42883
提示:没有函数匹配给定的名称和参数类型。您可能需要添加明确的类型转换。
上下文:PL / pgSQL函数dummytest_insert_trigger第8行在EXECUTE语句
我是无法获得错误。
您的函数可能在Postgres 9.0中看起来像这样:
CREATE OR REPLACE FUNCTION dummytest_insert_trigger()
RETURNS触发器AS
$ BODY $
DECLARE
v_partition_name text:= quote_ident( 'dummyTest'); - 在声明处分配
BEGIN
IF NEW.datetime不为空然后
EXECUTE
'INSERT INTO'|| v_partition_name || 'VALUES($ 1,$ 2)'
使用NEW.id,NEW.datetime;
END IF;
RETURN NULL; - 你确定吗?
END
$ BODY $
语言plpgsql;
强烈建议不要使用混合大小写标识符。使用格式(..%I ..)
或 quote_ident()
,您会得到一个名为dummyTest
,你必须在其余的时候加双引号。
用小写字母代替:
$ $ p $ code $ quote
>只要你有一个静态表名,使用
EXECUTE
动态SQL就没有意义了。但是这可能只是简单的例子吗? 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;
I'm trying to insert using 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.
Your function could look like this in Postgres 9.0:
CREATE OR REPLACE FUNCTION dummytest_insert_trigger()
RETURNS trigger AS
$BODY$
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
$BODY$
LANGUAGE plpgsql;
I would strongly 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.
Use lower case instead:
quote_ident('dummytest')
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?
这篇关于如何使用EXECUTE FORMAT ...在postgres函数中使用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!