postgresql – pl / pgsql块上的Prepared语句调用失败(语法错误

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) 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...

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

在PL / pgSQL函数中,您不使用预准备语句.实际上,函数中的每个语句都是“准备好的”,就像使用PREPARE命令的语句一样.在一个看起来有点奇怪的匿名代码块(PL / pgSQL)中,因为块只被使用一次然后被丢弃,但实际上它的工作原理是一样的. The principle is documented here并且引用了PREPARE语句,这是合乎逻辑的,因为行为基本相同：代码块中的语句被解析和缓存以供将来使用.也许有点多余,但考虑到这个问题的各种答案中的不知情的陈述,我想强调上面链接的文档中的一句话：

As each expression and SQL command is first executed in the function,the PL/pgSQL interpreter parses and analyzes the command to create a prepared statement

简而言之：在plpgsql代码块中显式创建预准备语句,然后动态执行该预准备语句是无用且低效且在概念上存在缺陷.

您要做的是准备一个语句,然后由后端准备该准备,然后您动态执行执行预准备语句的语句.这里没有讽刺意味：这就是发生的事情.另请注意,PL / pgSQL EXECUTE语句无任何缓存：SQL语句EXECUTE selectTematicasPlan(minRowNum,MAX_TEMS)在每次调用时都会被解析.这是一个相当简单的陈述,但是(如在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

  

  
   
  select min(X.rownum),maxRowNum from NTematica X; 

  

  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[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上.显然,您将获得结果作为常规表格数据,而不是1,000条通知.