本文旨在解决使用goose工具创建包含内部分号的postgresql函数时，常见的“未终止的美元引用字符串”错误。该错误通常源于goose对复杂sql语句的默认解析行为。教程将详细介绍如何通过添加`-- +goose statementbegin`和`-- +goose statementend`注解来正确标记这些语句，确保数据库迁移工具能够正确处理并执行函数创建操作，从而避免`pq`库报告的解析错误。
1. 问题背景与错误现象
在使用Goose进行PostgreSQL数据库迁移时，开发者可能会遇到在创建复杂函数（特别是包含$BODY$或$$美元引用字符串且内部含有多个分号的函数）时，系统报告pq: unterminated dollar-quoted string at or near "$BODY$..."的错误。尽管相同的SQL语句在pgAdmin等数据库客户端中可以成功执行，但在通过Goose执行迁移时却失败。这表明问题并非出在SQL语句本身的语法错误，而是Goose或其底层数据库驱动（如lib/pq）在解析此类语句时存在特定的处理机制。
示例错误信息：
(pq: unterminated dollar-quoted string at or near "$BODY$
BEGIN
LOOP
-- first try to update the key
UPDATE userslocations SET count = count+1 WHERE userid = user_id AND locationid = location_id;
"), quitting migration.2. 错误根源分析
Goose作为一款Go语言的数据库迁移工具，在处理SQL迁移文件时，会尝试解析并拆分文件中的各个SQL语句。其默认的解析逻辑通常以分号（;）作为语句的终止符。然而，PostgreSQL函数体内部（特别是使用美元引用字符串$BODY$或$$定义的函数体）可以包含任意数量的分号，这些分号是函数逻辑的一部分，并非独立的SQL语句分隔符。
当Goose遇到一个包含内部分号的函数定义时，它可能会错误地将函数体内部的分号识别为语句的结束，从而导致它在不恰当的位置截断SQL语句。这种错误的截断使得传递给lib/pq驱动的SQL不再是一个完整的、语法正确的函数定义，进而导致lib/pq报告“未终止的美元引用字符串”错误，因为它无法找到匹配的美元引用结束符。
3. 解决方案：Goose的特殊注解
为了解决这一问题，Goose提供了特殊的SQL注解，允许开发者明确指示哪些SQL块应该被视为一个完整的、不可分割的语句。这些注解是：
- -- +goose StatementBegin
- -- +goose StatementEnd
通过在复杂SQL语句的开始和结束位置添加这两个注解，Goose会忽略语句内部的分号，将整个被注解的块作为一个单一的SQL命令发送给数据库驱动执行。
4. 实施示例
以下是原始的PostgreSQL函数定义及其经过Goose注解修正后的版本。
原始问题代码：
CREATE OR REPLACE FUNCTION add_userlocation(user_id INT, location_id INT) RETURNS VOID AS
$BODY$
BEGIN
LOOP
UPDATE userslocations SET count = count+1 WHERE userid = user_id AND locationid = location_id;
IF found THEN
RETURN;
END IF;
BEGIN
INSERT INTO userslocations(userid,locationid, count) VALUES (user_id, location_id, 1);
RETURN;
EXCEPTION WHEN unique_violation THEN
END;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql;使用Goose注解修正后的代码：
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION add_userlocation(user_id INT, location_id INT) RETURNS VOID AS
$BODY$
BEGIN
LOOP
UPDATE userslocations SET count = count+1 WHERE userid = user_id AND locationid = location_id;
IF found THEN
RETURN;
END IF;
BEGIN
INSERT INTO userslocations(userid,locationid, count) VALUES (user_id, location_id, 1);
RETURN;
EXCEPTION WHEN unique_violation THEN
END;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql;
-- +goose StatementEnd通过添加-- +goose StatementBegin和-- +goose StatementEnd，Goose现在能够正确识别整个函数定义为一个原子操作，避免了内部解析错误。
5. 注意事项与最佳实践
- 适用范围： 并非所有SQL语句都需要这些注解。只有当SQL语句内部包含Goose默认解析器可能误判为语句终止符（如分号）的字符，且这些字符实际上是语句逻辑的一部分时，才需要使用StatementBegin/StatementEnd。这主要发生在创建函数、存储过程、触发器等复杂数据库对象时。
- 清晰性： 虽然注解解决了技术问题，但应保持SQL代码的清晰和可读性。将注解放置在SQL语句的紧邻行，使其意图明确。
- 版本控制： 将带有这些注解的迁移文件纳入版本控制系统，确保团队成员都能遵循相同的最佳实践。
- 测试： 在开发和生产环境中进行goose up和goose down测试，验证迁移的正确性和鲁棒性。
6. 总结
unterminated dollar-quoted string错误在使用Goose创建包含复杂逻辑（尤其是内部带有分号）的PostgreSQL函数时是一个常见挑战。理解其根源在于Goose的默认SQL解析机制，并利用-- +goose StatementBegin和-- +goose StatementEnd这两个关键注解，可以有效地解决这一问题。通过正确地标记复杂SQL语句，我们能够确保Goose将它们作为单一的原子单元进行处理，从而实现平滑、可靠的数据库迁移。遵循这些实践，将有助于构建更健壮的数据库迁移流程。
