How to Resolve \'Unterminated Dollar-Quoted String\' Error in PostgreSQL Functions Using Goose?

Unterminated Dollar-Quoted String Error Resolved with -- goose Annotations

An attempt to create a PostgreSQL function using Goose encounters an "unterminated dollar-quoted string" error. The function's code involves a looping construct, conditional checks, and a bounded exception block, which introduces semicolons within the statement.

Explanation

The Goose documentation specifies that complex SQL statements that include semicolons must be demarcated with the special annotations -- goose StatementBegin and -- goose StatementEnd. These annotations prevent Goose from modifying the SQL in a way that triggers errors with the pq database library.

Solution

To resolve the error, modify the function code to include the annotations as follows:

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

By annotating the start and end of the complex statement, Goose will handle the SQL processing appropriately, preventing the pq library from encountering the unterminated string error.

The above is the detailed content of How to Resolve \'Unterminated Dollar-Quoted String\' Error in PostgreSQL Functions Using Goose?. For more information, please follow other related articles on the PHP Chinese website!