在pl/pgsql块上，准备好的语句调用失败（语法错误和函数不存在）

Question

需要执行的原始PL/pgSQL匿名块代码如下：

do $$
declare
    tt integer[];
    minRowNum integer;
    maxRowNum integer;
    MIN_TEMS constant integer := 1;
    MAX_TEMS constant integer := 15;
    LAST_ARR_IDX constant integer := MAX_TEMS * 2;
    NUM_FILAS constant integer := 1000;
begin
    create temp table NTematica(rownum, tematica_id) as
    select
        S.n, (S.n * 841)::integer
    from
        generate_series(1,357) S(n);
    select
        min(X.rownum), max(X.rownum) into minRowNum, maxRowNum
    from
        NTematica X;
    prepare selectTematicasPlan(integer, integer, integer, integer) as
        select
            array_agg(X.tematica_id)
        from
            NTematica X
        where
            X.rownum in
            (
            select
                trunc(random() * ($2 - $1 + 1) + $1) :: integer
            from
                generate_series($3, trunc(random() * ($4 - $3 + 1) + $3) :: integer)
            );  
        for i in 1..NUM_FILAS loop
            execute selectTematicasPlan(minRowNum, maxRowNum, MIN_TEMS, MAX_TEMS);          
            raise notice 'First is % and % are the others', tt[1], tt[2:LAST_ARR_IDX];
    end loop;
    drop table NTematica cascade;
    deallocate selectTematicasPlan;
end$$;

然后，执行过程中出现错误：

ERROR:  syntax error at or near "("
LINE 34:       tt := execute selectTematicasPlan(minRowNum, maxRowNum...

然后，为了测试，我删除了 "tt :=" 并再次运行它，得到了以下结果：

ERROR:  function selecttematicasplan(integer, integer, integer, integer) does not exist
LINE 1: SELECT selectTematicasPlan(minRowNum, maxRowNum, MIN_TEMS, M...
               ^
HINT:  No function matches the given name and argument types. You might need to   add explicit type casts.

更新：

提前感谢大家。我要澄清一些问题：

我的初始代码是一个“插入选择”（包括CTE）来填充一个有1000万条记录的表。我可以确认它非常慢。因此，我决定选择一种命令式解决方案：创建一个带索引的未记录表（类似于映射数据结构），并循环1000万次以从“映射”中“执行”数据“选择”，然后进行数据“插入”。我将“select”和“insert”从循环中提取到准备好的语句中，因为这避免了PG的1000万个解析工作。发布的代码只涉及我使用准备好的语句时遇到的问题。

在pl/pgsql代码块中是否可以使用“prepare”+“execute”？PG文档（http://www.postgresql.org/docs/9.4/static/sql-prepare.html，http://www.postgresql.org/docs/9.4/static/sql-execute.html）没有对pl/pgsql进行说明（支持或反对）。

注：我的数据库版本（“select version()”）是： PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.9.2-10ubuntu13) 4.9.2, 64-bit

- Roger Dielrton

Answer 1

在PL/pgSQL函数中，您不使用预处理语句。实际上，函数中的每个语句都像使用PREPARE命令的语句一样被“准备”了。在匿名代码块（也是PL/pgSQL）中，这看起来有点奇怪，因为该块仅被使用一次然后被丢弃，但实际上它的工作方式完全相同。该原则在此处有记录，并提到了PREPARE语句，这是合乎逻辑的，因为行为本质上是相同的：代码块中的语句被解析并缓存以供将来使用。也许有些多余，但考虑到各种回答中的无知言论，我想强调一下上面链接的文档中的一句话：

由于在函数中首次执行每个表达式和SQL命令时，PL/pgSQL解释器会解析和分析该命令以创建一个准备好的语句

简而言之：在plpgsql代码块中明确创建准备好的语句，然后动态执行该准备好的语句是无用、低效和概念上错误的。

你正在尝试做的是准备一条语句，然后这个准备工作由后端处理，稍后动态执行该语句以执行已准备好的语句。这里没有讽刺意味：这就是发生的事情。请注意，PL/pgSQL的EXECUTE语句根本没有缓存：每次调用EXECUTE selectTematicasPlan(minRowNum, maxRowNum, MIN_TEMS, MAX_TEMS) SQL语句都会被解析。这是一个相当简单的语句，但（正如Klin's answer中所述），参数值必须在每次调用时传递，即使它们在每次调用时都相同。我希望你能看到这种方法的低效性（如果没有，那我就不说了）。

回到你的例子，你的函数应该像这样：

DO $$
DECLARE
  tt integer[];
  minRowNum integer := 1;
  maxRowNum integer := 357;
  MIN_TEMS constant integer := 1;
  MAX_TEMS constant integer := 15;
  NUM_FILAS constant integer := 1000;
BEGIN
  CREATE TEMP TABLE NTematica(rownum, tematica_id) AS
    SELECT S.n, (S.n * 841)::integer
    FROM generate_series(minRowNum, maxRowNum) S(n);

  -- generate_series() produces numbers from the first parameter to the last, inclusive
  -- no need to query for those values
<strike>        select
            min(X.rownum), max(X.rownum) into minRowNum, maxRowNum
        from
            NTematica X;</strike>

  FOR i IN 1..NUM_FILAS LOOP
    SELECT array_agg(X.tematica_id) INTO tt
    FROM NTematica X
    WHERE X.rownum IN (
      SELECT trunc(random() * (maxRowNum - minRowNum + 1) + minRowNum)::integer
      FROM generate_series(MIN_TEMS, trunc(random() * (MAX_TEMS - MIN_TEMS + 1) + MIN_TEMS)::integer)
    );  
    RAISE NOTICE 'First is % and % are the others', tt[1], tt[2:array_upper(tt)];
  END LOOP;
  DROP TABLE NTematica;
END; $$;

您还可以将匿名代码块编写为单个 SQL 语句，如下所示，使用 CTE 来提高可读性：

WITH params(minRowNum integer, maxRowNum integer, MIN_TEMS integer, MAX_TEMS integer) AS
  SELECT 1, 357, 1, 15
), rowNums(rwNum integer, tematica_id integer) AS (
  SELECT S.n, (S.n * 841)::integer
  FROM params, generate_series(params.minRowNum, params.maxRowNum) S(n)
)
SELECT tt[1] AS first, tt[2:array_upper(tt)] AS rest
FROM generate_series(1, 1000) ON true
JOIN (
  SELECT array_agg(rw.tematica_id) AS tt
  FROM params p, rowNums rw
  WHERE rw.rwNum IN (
    SELECT trunc(random() * (p.maxRowNum - p.minRowNum + 1) + p.minRowNum)::integer
    FROM generate_series(p.MIN_TEMS, trunc(random() * (p.MAX_TEMS - p.MIN_TEMS + 1) + p.MIN_TEMS)::integer)
  ) agg ON true;

多次使用的参数都在顶部一行中，因此易于修改且不存在不一致的风险。这应该比匿名代码块快得多，因为您会失去很多开销，尤其是在TEMP TABLE上。显然，您将获得常规表格数据而不是1000个通知。

- Patrick

Answer 2

这是不应该工作的，你不能在PL/pgSQL中使用SQL命令来准备语句。这是无意义的 - 在PLpgSQL中使用的每个嵌入式SQL语句默认都是准备好的语句。

- Pavel Stehule

---

请告诉我Postgres文档的URL，其中说明“prepare”和“execute”不能在pL/pgSQL块内使用。 - Roger Dielrton

可能它没有记录——这只是因为plpgsql有EXECUTE语句，而不是SQL语句EXECUTE。因此，如果没有丑陋的解决方法，你无法做到这一点。 - Pavel Stehule

@RogerDielrton: 请查阅此处的文档：http://www.postgresql.org/docs/9.4/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING - Patrick