Home > Backend Development > Golang > How to Resolve \'Unterminated Dollar-Quoted String\' Errors in PostgreSQL Functions with Goose?

How to Resolve \'Unterminated Dollar-Quoted String\' Errors in PostgreSQL Functions with Goose?

Mary-Kate Olsen
Release: 2024-11-02 15:10:03
Original
302 people have browsed it

How to Resolve

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;
Copy after login

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

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template