How to Fix \'Unterminated Dollar-Quoted String\' Error When Creating PostgreSQL Functions with Goose?

Troubleshooting "Unterminated Dollar-Quoted String" Error When Creating PostgreSQL Function with Goose

When attempting to create a function with Goose using a PostgreSQL database, you may encounter the 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;
")

This error arises due to the presence of semicolons within the SQL statement. Goose expects complex statements, including those with semicolons, to be annotated with specific comments.

To resolve the issue, annotate the statement as follows:

<code class="sql">   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;</code>

The -- goose StatementBegin and -- goose StatementEnd comments instruct Goose to handle the statement correctly, preventing the issue with unterminated dollar-quoted strings.

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