使用Golang lib/pq向PostgreSQL DO块传参失败的问题求助

问题场景 #

需要实现单查询逻辑：若segment表中存在指定slug且is_active为true的记录，则抛出异常；否则插入该slug，若已存在则将其is_active设为true。

最初尝试的PL/pgSQL匿名块：

DO $$ BEGIN 
    IF EXISTS (SELECT * FROM segment WHERE slug = $1 AND is_active = true) THEN
        RAISE EXCEPTION $2; 
    ELSE 
        INSERT INTO segment (slug) VALUES ($1)
        ON CONFLICT(slug) DO 
            UPDATE SET is_active = true; 
    END IF; 
END $$;

通过Golang的database/sql和lib/pq库执行该语句并传入slug、exceptionMsg两个参数时，报错：

pq: got 2 parameters but the statement requires 0

若去掉$$包裹直接执行IF语句，会触发语法错误：

pq: syntax error at or near "IF"

问题原因 #

1. 单独的IF语句属于PL/pgSQL语法，不能作为顶层SQL语句执行，必须包裹在DO $$ ... $$形式的匿名块中。
2. DO语句本身不支持直接识别块内部的参数占位符，驱动会将整个DO语句视为无参数语句，导致传入的参数不被识别，引发参数数量不匹配错误。

解决方案 #

方案1：带参数的匿名块（使用USING子句） #

通过USING子句向匿名块传递参数，块内声明变量接收参数后使用：

DO $$ 
DECLARE
    p_slug text := $1;
    p_exception_msg text := $2;
BEGIN 
    IF EXISTS (SELECT * FROM segment WHERE slug = p_slug AND is_active = true) THEN
        RAISE EXCEPTION '%', p_exception_msg; 
    ELSE 
        INSERT INTO segment (slug) VALUES (p_slug)
        ON CONFLICT(slug) DO 
            UPDATE SET is_active = true; 
    END IF; 
END $$ USING $1, $2;

对应的Golang执行代码：

_, err := p.Exec(`
    DO $$ 
    DECLARE
        p_slug text := $1;
        p_exception_msg text := $2;
    BEGIN 
        IF EXISTS (SELECT * FROM segment WHERE slug = p_slug AND is_active = true) THEN
            RAISE EXCEPTION '%', p_exception_msg; 
        ELSE 
            INSERT INTO segment (slug) VALUES (p_slug)
            ON CONFLICT(slug) DO 
                UPDATE SET is_active = true; 
        END IF; 
    END $$ USING $1, $2;`,
slug,
exceptionMsg,
)

方案2：使用存储过程（PostgreSQL 11+） #

创建带参数的存储过程，直接调用即可传递参数：

CREATE OR REPLACE PROCEDURE upsert_segment(p_slug text, p_exception_msg text)
LANGUAGE plpgsql
AS $$
BEGIN
    IF EXISTS (SELECT * FROM segment WHERE slug = p_slug AND is_active = true) THEN
        RAISE EXCEPTION '%', p_exception_msg;
    ELSE
        INSERT INTO segment (slug) VALUES (p_slug)
        ON CONFLICT(slug) DO UPDATE SET is_active = true;
    END IF;
END $$;

Golang调用代码：

_, err := p.Exec("CALL upsert_segment($1, $2)", slug, exceptionMsg)

方案3：纯SQL替代PL/pgSQL逻辑 #

通过INSERT ... ON CONFLICT结合条件判断，避免使用PL/pgSQL，再在Golang中根据受影响行数判断是否抛出异常：

INSERT INTO segment (slug, is_active) VALUES ($1, true)
ON CONFLICT(slug) DO UPDATE 
    SET is_active = true
WHERE NOT EXISTS (SELECT 1 FROM segment WHERE slug = $1 AND is_active = true);

Golang代码：

result, err := p.Exec(`
    INSERT INTO segment (slug, is_active) VALUES ($1, true)
    ON CONFLICT(slug) DO UPDATE 
        SET is_active = true
    WHERE NOT EXISTS (SELECT 1 FROM segment WHERE slug = $1 AND is_active = true);`,
slug,
)
if err != nil {
    // 处理错误
}
rowsAffected, err := result.RowsAffected()
if err != nil {
    // 处理错误
}
if rowsAffected == 0 {
    err = fmt.Errorf("%s", exceptionMsg)
}

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