Question 376585 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – ERROR: syntax error at or near "QUERY" QUERY EXECUTE 'set enable_parallel_append = off';

I have below plpgsql function –

CREATE OR REPLACE FUNCTION public.end_to_end_query(part_id integer)
 LANGUAGE plpgsql IMMUTABLE
AS $function$
DECLARE
  query_pw varchar := format(
    $dynsql$
    ALTER TABLE metric_events_%s SET (parallel_workers = 16)
    $dynsql$, part_id
  );

BEGIN
    RAISE NOTICE 'Value: %', query;
    QUERY EXECUTE 'set enable_parallel_append = off';
    QUERY EXECUTE "SET work_mem TO '1GB'";
    QUERY EXECUTE query_pw;
    QUERY EXECUTE "SET max_parallel_workers_per_gather = 16";
    QUERY EXECUTE "set max_worker_processes=8";
END $function$;

When I run the above function, I get the error –

ERROR: syntax error at or near "QUERY"

LINE 13: QUERY EXECUTE ‘set enable_parallel_append = off’;
^

SQL state: 42601

Character: 1419

How can I fix this error?

Tags:

2

Answers


  1. 0

    query execute is not a valid SQL command, you just need to call execute:

    CREATE OR REPLACE FUNCTION public.end_to_end_query(part_id integer)
 LANGUAGE plpgsql IMMUTABLE
AS $function$
DECLARE
  query_pw varchar := format(
    $dynsql$
    ALTER TABLE metric_events_%s SET (parallel_workers = 16)
    $dynsql$, part_id
  );

BEGIN
    RAISE NOTICE 'Value: %', query;
    EXECUTE 'set enable_parallel_append = off';
    EXECUTE 'SET work_mem TO ''1GB''';
    EXECUTE query_pw;
    EXECUTE "SET max_parallel_workers_per_gather = 16";
    EXECUTE "set max_worker_processes=8";
END $function$;
    Login or Signup to reply.
  2. 0

    The PL/pgSQL command to execute dynamic query strings is EXECUTE, not QUERY EXECUTE. (Also available in the combined form RETURN QUERY EXECUTE to actually return the results of a dynamic query.)

    But this is only one of multiple grave errors in the function. This could work:

    CREATE OR REPLACE FUNCTION public.end_to_end_query(part_id integer)
  RETURNS void
  LANGUAGE plpgsql VOLATILE AS
$func$
DECLARE
   _query text := format($x$ALTER TABLE metric_events_%s SET (parallel_workers = 16)$x$, part_id);
BEGIN
   IF part_id IS NULL THEN
      RAISE EXCEPTION 'A notnull part_id must be given!';
   END IF;

   SET enable_parallel_append = off;
   SET work_mem = '1GB';
   SET max_parallel_workers_per_gather = 16;
   -- SET max_worker_processes = 8;  -- double nonsense!

   RAISE NOTICE 'Query: %', _query;
   EXECUTE _query;
END
$func$;

    You cannot create a function without declaring the return type, typically with a RETURNS clause. You may want a PROCEDURE instead. See:

    Double-quotes are not for string literals. See:

    You do not need EXECUTE for plain, static SET commands.

    You cannot set max_worker_processes in a function like this. It requires a server restart.

    It is nonsense to set max_worker_processes lower than max_parallel_workers_per_gather.

    The function is VOLATILE, not IMMUTABLE.

    You must defend against NULL input, or the dynamic query string either raises an exception or does something unexpected.

    Mixing those general SET commands with setting a table option seems odd. Are you sure, you want to package that in a single function?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search