Unterminated Dollar-Quoted String: Resolving Errors with Semicolons

In the context of creating a PostgreSQL function with Goose, this article addresses an error encountered while processing a complex statement within the function body. The error, reported by the pq library, indicates that a dollar-quoted string remains unterminated.

To resolve this issue, note that complex statements featuring semicolons require annotation using "-- goose StatementBegin" and "-- goose StatementEnd" annotations, as per the Goose documentation. These annotations assist Goose in managing embedded semicolons within SQL statements, preventing libpq errors.

Applying these annotations to the provided code sample resolves the error:

CREATE OR REPLACE FUNCTION add_userlocation(user_id INT, location_id INT) RETURNS VOID AS
$BODY$
--  goose StatementBegin
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;
--  goose StatementEnd
END;
$BODY$
LANGUAGE plpgsql;