与PostgreSQL一起使用时，EF核心迁移脚本错误

Question

我的迁移脚本以下。我不知道为什么仅运行创建或替换函数 block> block它起作用。

DO $$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20190305152943_SupportLocationAndAsset') THEN
    CREATE OR REPLACE FUNCTION build_hierarchey(location_id int default null::int, prefer_depth int default null::int) 
    RETURNS SETOF jsonb AS 
    $$
    BEGIN
        IF prefer_depth < 0 THEN
            RETURN;
        ELSE 
            prefer_depth := prefer_depth - 1;
            RETURN QUERY 
            SELECT
                CASE WHEN COUNT(x) > 0 
                    THEN (((to_jsonb(t) || jsonb_build_object('Children', jsonb_agg(f.x))) - 'SearchVector') - 'ParentLocationId')
                    ELSE ((to_jsonb(t) - 'SearchVector') - 'ParentLocationId')
                END
            FROM "Locations" t
            LEFT JOIN build_hierarchey(t."Id", prefer_depth) AS f(x) ON true
            WHERE t."ParentLocationId" = location_id OR (location_id IS null AND t."ParentLocationId" IS null)
            GROUP BY t."Id", t."Name";  
        END IF; 
        RETURN;
    END;
    $$ LANGUAGE plpgsql
    END IF;
END $$;

错误

ERROR:  syntax error at or near "BEGIN"
LINE 8:  BEGIN
         ^
SQL state: 42601
Character: 286

Answer 1

问题与定界符是在if条件

内重复

DO $$
IF NOT EXISTS .....
$func$
     BEGIN
     SQL/FUNCTION/TRIGGER
END;$func$
END $$;