How to Fix the \"Unterminated String\" Error When Creating Stored Procedures with Goose and PostgreSQL?

Postgres Goose Raises Unterminated String Error

Issue

When attempting to create a stored procedure with Goose using a PostgreSQL database, the process encounters the following error:

(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.

Cause

Goose's error echoing from the pq library indicates misformatted SQL. Specifically, complex statements containing semicolons require special annotation in Goose:

Resolution

To rectify the error, annotate the SQL in the following manner:

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;

By declaring the beginning and end of each statement, Goose is instructed to handle the SQL properly, resolving the unterminated string error.

The above is the detailed content of How to Fix the \"Unterminated String\" Error When Creating Stored Procedures with Goose and PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!