postgresql pl/pgSQL EXECUTE 语句中没有参数 $1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19302098/
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
Pl/pgSQL there is no parameter $1 in EXECUTE statement
提问by stefmex
I can't solve this:
我无法解决这个问题：
CREATE OR REPLACE FUNCTION dpol_insert(
dpol_cia integer, dpol_tipol character, dpol_nupol integer,
dpol_conse integer,dpol_date timestamp)
RETURNS integer AS
$BODY$
DECLARE tabla text := 'dpol'||EXTRACT (YEAR FROM ::timestamp);
BEGIN
EXECUTE '
INSERT INTO '|| quote_ident(tabla) ||'
(dpol_cia, dpol_tipol, dpol_nupol, dpol_conse, dpol_date) VALUES (,,,,)
';
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
When trying
尝试时
SELECT dpol_insert(1,'X',123456,1,'09/10/2013')
return next message:
返回下一条消息：
ERROR: there is no parameter
LINE 3: ...tipol, dpol_nupol, dpol_conse, dpol_date) VALUES (,,,$...
^
QUERY:
INSERT INTO dpol2013
(dpol_cia, dpol_tipol, dpol_nupol, dpol_conse, dpol_date) VALUES (,,,,)
CONTEXT: PL/pgSQL function "dpol_insert" line 4 at EXECUTE statement
***Error ***
***错误 ** *
ERROR: there is no parameter
SQL state: 42P02
Context: PL/pgSQL function "dpol_insert" line 4 at EXECUTE statement
回答by mu is too short
You have a couple problems here. The immediate problem is:
你在这里有几个问题。眼前的问题是：
ERROR: there is no parameter $1
错误：没有参数 $1
That happens because $1inside the SQL that you're handing to EXECUTE isn't the same as $1inside the main function body. The numbered placeholders within the EXECUTE SQL are in the context of the EXECUTE, not in the function's context so you need to supply some arguments to EXECUTE for those placeholders:
发生这种情况是因为$1您传递给 EXECUTE 的 SQL$1内部与主函数体内部不同。EXECUTE SQL 中的编号占位符在 EXECUTE 的上下文中，而不是在函数的上下文中，因此您需要为这些占位符为 EXECUTE 提供一些参数：
execute '...' using dpol_cia, dpol_tipol, dpol_nupol, dpol_conse, dpol_date;
-- ^^^^^
See Executing Dynamic Commandsin the manual for details.
有关详细信息，请参阅手册中的执行动态命令。
The next problem is that you're not returning anything from your function which RETURNS integer. I don't know what you intend to return but maybe your tableahas a SERIAL idthat you'd like to return. If so, then you want something more like this:
下一个问题是你没有从你的函数返回任何东西 which RETURNS integer. 我不知道您打算返回什么，但也许您tablea有想要返回的序列号id。如果是这样，那么您想要更像这样的东西：
declare
tabla text := 'dpol' || extract(year from ::timestamp);
id integer;
begin
execute 'insert into ... values (, ...) returning id' into id using dpol_cia, ...;
-- ^^^^^^^^^^^^ ^^^^^^^
return id;
end
in your function.
在你的函数中。
