[SOLVED] Unterminated dollar-quoted string reported by goose when creating PostgreSQL function

Unterminated dollar-quoted string reported by goose when creating PostgreSQL function

I'm trying to create this function with Goose using a postgres (pq lib) database.

My code is as follows:

   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;

When I try to goose up it provides an 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.

Goose basically echo's the pq library error, so I dont think it's in Goose, but rather the pq-library. Query runs succesful on pgAdmin III.

Solution

According to the goose documentation, complex statements that include semicolons must be annotated with -- +goose StatementBegin and -- +goose StatementEnd

Your statement contains semicolons embedded within it so you need to use these annotations. Otherwise goose mangles the SQL so that libpq gives errors.