如何在C# Npgsql中执行含DO/DECLARE的PostgreSQL脚本？

直接适配现有代码的修复方案 #

PostgreSQL的DO匿名块无法直接访问外部传入的参数，必须通过USING子句显式传递参数，并在块内声明对应变量接收。修改PoliciesInsert的SQL语句如下：

public static string PoliciesInsert() =>
@$"DO $$
    DECLARE 
        policy_text varchar(100);
        v_policy varchar(100) := $1;
        v_description varchar(255) := $2;
        v_roles int[] := $3;
        v_user_id int := $4;
    BEGIN
        INSERT INTO common_sch.policies(policy, description)
        VALUES(v_policy, v_description)
        ON CONFLICT ON CONSTRAINT policies_pk
        DO UPDATE SET is_deleted = false
        RETURNING policy INTO policy_text;

        INSERT INTO common_sch.policy_to_role(role_id, policy, created_by, updated_by)
            SELECT 
                unnest(v_roles) as role_id, 
                policy_text as policy, 
                v_user_id as created_by, 
                v_user_id as updated_by
        ON CONFLICT ON CONSTRAINT policy_to_role_unique
            DO UPDATE SET is_deleted = false, updated_by = v_user_id;
    END $$ USING @_Policy, @_Description, @_Roles, @_UserId;";

注意：DO块本身不支持直接返回结果，若需要获取返回值，需额外调整逻辑（比如将结果插入临时表后在块外查询），但这种方式较为繁琐。

更优方案：改用PostgreSQL函数 #

改用函数可以彻底解决参数绑定问题，同时支持直接返回结果，更符合PostgreSQL最佳实践：

1. 创建PostgreSQL函数 #

CREATE OR REPLACE FUNCTION common_sch.insert_policies(
    p_policy varchar(100),
    p_description varchar(255),
    p_roles int[],
    p_user_id int
) RETURNS varchar AS $$
DECLARE
    policy_text varchar(100);
BEGIN
    INSERT INTO common_sch.policies(policy, description)
    VALUES(p_policy, p_description)
    ON CONFLICT ON CONSTRAINT policies_pk
    DO UPDATE SET is_deleted = false
    RETURNING policy INTO policy_text;

    INSERT INTO common_sch.policy_to_role(role_id, policy, created_by, updated_by)
        SELECT 
            unnest(p_roles) as role_id, 
            policy_text as policy, 
            p_user_id as created_by, 
            p_user_id as updated_by
    ON CONFLICT ON CONSTRAINT policy_to_role_unique
        DO UPDATE SET is_deleted = false, updated_by = p_user_id;
    
    RETURN policy_text;
END;
$$ LANGUAGE plpgsql;

2. 修改C#查询语句 #

public static string PoliciesInsert() => 
    "SELECT common_sch.insert_policies(@_Policy, @_Description, @_Roles, @_UserId);";

额外注意事项 #

• 原Insert<int>方法的泛型类型为int，但实际返回的是policy字符串（varchar），需根据需求调整为Insert<string>。
• 函数方式的可维护性、可读性更强，后续修改逻辑只需调整函数即可，无需改动C#代码中的SQL字符串。

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