如何在PL/pgSQL的DO $$块中使用含$$的字符串值

你遇到的错误是：错误：在"9"附近或存在语法错误，根源是PostgreSQL的美元符引用机制，把密码哈希里的$p5k2$识别成了新的字符串开始标记，和DO块外层的$$分隔符产生解析冲突。

以下是两种可行的解决方法：

1. 自定义DO块的分隔符 #

选一个不会出现在密码哈希或SQL内容里的字符串作为分隔符（比如MY_CUSTOM_BLOCK），用$MY_CUSTOM_BLOCK$包裹整个DO块，避免内部特殊$结构被误解析：

DO $MY_CUSTOM_BLOCK$ DECLARE parent_user int; BEGIN 
CREATE TEMP TABLE IF NOT EXISTS tmp_f6a8e872_b346_4184_889d_52ffeded463f (id int); 
INSERT INTO form_user(firstname,lastname,displayname,job_title,username,authentication_type,password,password2,password_reset,role,comm_portal,issue_ticket,email,rec_sms,created_by,created_on,auto_name) 
VALUES ('delete','delete','delete','admin','delete','password','$p5k2$$9kenbgnr$XuoSekYV4qQlDE0NkNEs2UGJEEIWtjG/','$p5k2$$7cxgxz4n$JSkgV8oGg72S1O6i2xYZSLEU2Yd6bdCO','0','admin','No','No','delete@delete.com','No',22,'11/5/2023 19:59:45','delete delete (admin)') 
RETURNING id INTO parent_user; 
INSERT INTO tmp_f6a8e872_b346_4184_889d_52ffeded463f (id) VALUES (parent_user); 
END; $MY_CUSTOM_BLOCK$;

2. 使用参数化查询（推荐） #

通过EXECUTE ... USING语法分离SQL模板和参数，完全规避字符串拼接带来的特殊字符问题，同时还能防止SQL注入：

DO $$ DECLARE parent_user int; BEGIN 
CREATE TEMP TABLE IF NOT EXISTS tmp_f6a8e872_b346_4184_889d_52ffeded463f (id int); 
EXECUTE '
    INSERT INTO form_user(
        firstname,lastname,displayname,job_title,username,
        authentication_type,password,password2,password_reset,role,
        comm_portal,issue_ticket,email,rec_sms,created_by,
        created_on,auto_name
    ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17)
    RETURNING id'
INTO parent_user
USING 
    'delete','delete','delete','admin','delete','password',
    '$p5k2$$9kenbgnr$XuoSekYV4qQlDE0NkNEs2UGJEEIWtjG/',
    '$p5k2$$7cxgxz4n$JSkgV8oGg72S1O6i2xYZSLEU2Yd6bdCO',
    '0','admin','No','No','delete@delete.com','No',22,
    '11/5/2023 19:59:45','delete delete (admin)';
INSERT INTO tmp_f6a8e872_b346_4184_889d_52ffeded463f (id) VALUES (parent_user); 
END; $$;

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