PostgreSQL DO块中如何转义$$符号？pg_cron调度pgpartman报错

问题场景 #

尝试通过PostgreSQL的cron扩展调度pgpartman的维护任务，编写了一段可重复执行的DO块代码，但执行时触发语法错误。

原DO块代码：

do $$
declare
   schemaname text := 'mytest';
begin
    execute 'set search_path to ' || schemaname||', public';
    if not exists (select * from cron.job where command like '%call run_maintenance_proc%') then 
        select cron.schedule('@daily',$$call run_maintenance_proc()$$);
        raise notice 'sqltext - %', sqltext;
        execute sqltext;
    end if;
end;
$$;

执行时的报错信息：

postgres=> do $$
postgres$> declare
postgres$>    schemaname text := 'nmtest';
postgres$>    sqltext text;
postgres$> begin
postgres$>     execute 'set search_path to ' || schemaname||', public';
postgres$>     if not exists (select * from cron.job where command like '%call run_maintenance_proc%') then
postgres$> select cron.schedule('@daily',$$call run_maintenance_proc()$$);
postgres$>     raise notice 'sqltext - %', sqltext;
postgres$>     execute sqltext;
postgres$>     end if;
postgres$> end;
postgres$> $$;
ERROR:  syntax error at or near "call"
LINE 8: select cron.schedule('@daily',$$call run_maintenance_proc()$...
                                        ^

单独执行调度语句是正常的：

-- works fine as regular call
postgres=> select cron.schedule('@daily', $$call partman.run_maintenance_proc()$$);
 schedule
----------
        4
(1 row)

错误核心原因：DO块使用$$作为代码块分隔符，内部调用cron.schedule时的$$被PostgreSQL误判为DO块的结束标记，导致语法解析失败。

解决方法 #

方法1：使用自定义美元引号分隔符 #

PostgreSQL允许为美元引号添加自定义标签，彻底避免内部分隔符和外部DO块的分隔符冲突。比如将内部的$$替换为$cron_cmd$这类带标签的分隔符：

修正后的完整DO块代码：

do $$
declare
   schemaname text := 'mytest';
   job_id integer;
begin
    execute 'set search_path to ' || schemaname || ', public';
    if not exists (select * from cron.job where command like '%call run_maintenance_proc%') then 
        -- 使用带标签的美元引号分隔符$cron_cmd$
        select cron.schedule('@daily', $cron_cmd$call run_maintenance_proc()$cron_cmd$) into job_id;
        raise notice 'Scheduled maintenance job with ID: %', job_id;
    end if;
end;
$$;

方法2：使用单引号转义 #

将cron.schedule的第二个参数改用单引号包裹，若参数内部存在单引号，只需用两个单引号转义即可（此场景无内部单引号，直接使用）：

do $$
declare
   schemaname text := 'mytest';
   job_id integer;
begin
    execute 'set search_path to ' || schemaname || ', public';
    if not exists (select * from cron.job where command like '%call run_maintenance_proc%') then 
        select cron.schedule('@daily', 'call run_maintenance_proc()') into job_id;
        raise notice 'Scheduled maintenance job with ID: %', job_id;
    end if;
end;
$$;

额外逻辑修正 #

原代码中sqltext变量未赋值就执行execute sqltext属于逻辑错误，上述修正代码已移除无效的sqltext相关代码，直接将调度任务ID存入变量用于日志输出。

内容的提问来源于stack exchange，提问作者nmakb