pl/pgSQL EXECUTE语句中没有参数$ 1 [英] Pl/pgSQL there is no parameter $1 in EXECUTE statement

问题描述

我不能解决这个问题:

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 $5::timestamp);
BEGIN
    EXECUTE '
    INSERT INTO '|| quote_ident(tabla) ||' 
    (dpol_cia, dpol_tipol, dpol_nupol, dpol_conse, dpol_date) VALUES ($1,$2,$3,$4,$5)
    ';
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

---

尝试时

---

When trying

SELECT dpol_insert(1,'X',123456,1,'09/10/2013')

返回下一条消息:

ERROR:  there is no parameter $1
LINE 3: ...tipol, dpol_nupol, dpol_conse, dpol_date) VALUES ($1,$2,$3,$...
                                                             ^
QUERY:  
    INSERT INTO dpol2013 
    (dpol_cia, dpol_tipol, dpol_nupol, dpol_conse, dpol_date) VALUES ($1,$2,$3,$4,$5)

CONTEXT:  PL/pgSQL function "dpol_insert" line 4 at EXECUTE statement

*** 错误 ** *

*** Error ***

ERROR: there is no parameter $1
SQL state: 42P02
Context: PL/pgSQL function "dpol_insert" line 4 at EXECUTE statement

推荐答案

您在这里遇到了几个问题.眼前的问题是:

You have a couple problems here. The immediate problem is:

错误:没有参数$ 1

ERROR: there is no parameter $1

之所以会发生这种情况，是因为您要传递给EXECUTE的SQL中的$1与主函数主体中的$1不同. EXECUTE SQL中带编号的占位符是在EXECUTE的上下文中，而不是在函数的上下文中，因此您需要为这些占位符提供一些自变量给EXECUTE:

That happens because $1 inside the SQL that you're handing to EXECUTE isn't the same as $1 inside 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:

execute '...' using dpol_cia, dpol_tipol, dpol_nupol, dpol_conse, dpol_date;
--            ^^^^^

请参见执行动态命令.

下一个问题是您没有从函数RETURNS integer返回任何内容.我不知道您打算退货什么，但是您的tablea可能有您想要退货的SERIAL id.如果是这样，那么您想要更多类似这样的东西:

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 tablea has a SERIAL id that you'd like to return. If so, then you want something more like this:

declare
    tabla text := 'dpol' || extract(year from $5::timestamp);
    id integer;
begin
    execute 'insert into ... values ($1, ...) returning id' into id using dpol_cia, ...;
    --                                        ^^^^^^^^^^^^  ^^^^^^^
    return id;
end

在您的功能中.

这篇关于pl/pgSQL EXECUTE语句中没有参数$ 1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！