PostgreSQL中dblink_exec遍历所有库创建函数报错求助

原代码报错的核心原因是函数体的$$分隔符与外层DO块的$$冲突，导致PostgreSQL解析SQL时出现语法混淆，无法正确识别函数内部的DECLARE关键字。以下是修正后的批量创建函数方案：

修正后的SQL代码 #

CREATE EXTENSION IF NOT EXISTS dblink;

DO 
$$
DECLARE
    database_name TEXT;
    conn_template TEXT;
    conn_string TEXT;
    query_string TEXT;
BEGIN
    conn_template = 'user=postgres_user password=postgres_password dbname=';
    
    -- 遍历所有非模板数据库
    FOR database_name IN
        SELECT datname FROM pg_database
        WHERE datistemplate = false
    LOOP
        conn_string = conn_template || database_name;
        
        -- 1. 确保datadog模式存在（若不存在则创建）
        PERFORM dblink_exec(conn_string, 'CREATE SCHEMA IF NOT EXISTS datadog;');
        
        -- 2. 创建目标函数，使用$func$作为函数体分隔符避免冲突
        query_string = 'CREATE OR REPLACE FUNCTION datadog.explain_statement(
                        l_query TEXT,
                        OUT explain JSON
                        )
                        RETURNS SETOF JSON AS
                        $func$
                        DECLARE
                        curs REFCURSOR;
                        plan JSON;
                        BEGIN
                        OPEN curs FOR EXECUTE pg_catalog.concat(''EXPLAIN (FORMAT JSON) '', l_query);
                        FETCH curs INTO plan;
                        CLOSE curs;
                        RETURN QUERY SELECT plan;
                        END;
                        $func$
                        LANGUAGE ''plpgsql''
                        RETURNS NULL ON NULL INPUT
                        SECURITY DEFINER;';
        
        PERFORM dblink_exec(conn_string, query_string);
    END LOOP;
END
$$;

关键修改点说明 #

• 替换函数体分隔符：将原函数体的$$改为$func$，彻底避免与外层DO块的$$产生语法解析冲突。
• 增加模式检查：新增CREATE SCHEMA IF NOT EXISTS datadog;语句，确保目标数据库中存在函数所属的模式，避免因模式缺失导致创建失败。
• 移除无效format调用：原代码中format(query_string)无实际作用（无占位符需要替换），移除后避免不必要的字符串解析干扰。

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