The prepared call request is not executed (syntax error and function does not exist) in the pl / pgsql block

Question

The original anonymous PL / pgSQL block, the code to execute is as follows:

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$$;

Then the execution will fail:

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

Then for testing, I remove "tt: =" and run it again using this result:

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.

Update:

Thanks in advance to everyone. I want to clarify some questions:

" select" ( CTE) 10 . , . , IMPERATIVE: ( ) 10 , "" "" "", "" , "" "" , 10 PG. . "" + "" pl/pgsql? PG doc (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 x86_64-unknown-linux-gnu, gcc (Ubuntu 4.9.2-10ubuntu13) 4.9.2, 64-

Answer 1

PL/pgSQL . , "", PREPARE. ( PL/pgSQL), , , , . , PREPARE, , : . , , , , , :

SQL , PL/pgSQL

: plpgsql, .

, , - , , . : . , PL/pgSQL EXECUTE : SQL EXECUTE selectTematicasPlan(minRowNum, maxRowNum, MIN_TEMS, MAX_TEMS) . , ( Klin 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), max(X.rownum) into minRowNum, 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[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 .

Answer 2

, SQL PL/pgSQL. - SQL, PLpgSQL, .