Postgres触发器函数在Play框架数据库演化中美元引号未终止错误解决

问题根源 #

Play框架的数据库演化脚本解析器对PostgreSQL的**美元引号（$$）**语法兼容性不足，它会将美元符号识别为未终止的引号起始标记，从而抛出SQLSTATE:42601语法错误；而psql客户端原生支持该语法，因此直接执行不会出现问题。

可行解决方法 #

方法1：替换美元引号为转义单引号 #

将函数体的美元引号替换为普通单引号，同时对函数内部的单引号进行转义（用两个单引号''替代单个单引号'）。

原函数示例（使用美元引号）：

CREATE OR REPLACE FUNCTION task_state_change_stamp()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.state != OLD.state THEN
        NEW.state_changed_at = CURRENT_TIMESTAMP;
        RETURN NEW;
    END IF;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

修改后版本：

CREATE OR REPLACE FUNCTION task_state_change_stamp()
RETURNS TRIGGER AS '
BEGIN
    IF NEW.state != OLD.state THEN
        NEW.state_changed_at = CURRENT_TIMESTAMP;
        RETURN NEW;
    END IF;
    RETURN OLD;
END;
' LANGUAGE plpgsql;

若函数体包含单引号（如RAISE NOTICE 'State updated'），需转义为RAISE NOTICE ''State updated''。

方法2：使用自定义美元引号标记 #

如果不想大量转义单引号，可以改用自定义的美元引号标记（如$trigger_func$），同时确保Play框架将完整SQL直接传递给PostgreSQL执行，而非提前解析引号结构。

修改后示例：

CREATE OR REPLACE FUNCTION task_state_change_stamp()
RETURNS TRIGGER AS $trigger_func$
BEGIN
    IF NEW.state != OLD.state THEN
        NEW.state_changed_at = CURRENT_TIMESTAMP;
        RETURN NEW;
    END IF;
    RETURN OLD;
END;
$trigger_func$ LANGUAGE plpgsql;

方法3：改用Play原生SQL执行逻辑 #

若脚本解析限制无法绕过，可将触发器函数的创建逻辑从纯SQL脚本转为Play代码（Java/Kotlin类），通过框架的原生SQL执行API直接发送语句到PostgreSQL，避免Play解析器处理美元引号。

验证步骤 #

修改完成后，先在psql客户端测试执行确保函数功能正常，再放入Play演化脚本中运行，避免语法错误中断数据库迁移流程。

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