ERROR: unterminated quoted string at or near

ant, database, plpgsql, postgresql, triggers

Solution

I encountered this error in liquibase and this page was one of the first search results so I guess I share my solution at this page:

You can put your whole sql in a separate file and include this in the changeset. Its important to set the `splitStatements` option to `false`.

The whole changeset would then look like

<changeSet author="fgrosse" id="530b61fec3ac9">
    <sqlFile path="your_sql_file_here.sql" splitStatements="false"/>
</changeSet>

I always like to have those big SQL parts (like function updates and such) in separate files. This way you get proper syntax highlighting when opening the sql file and dont have to intermix XML and SQL in one file.

Edit: as mentioned in the comments its worth noting that the `sql` change supports the `splitStatements` option as well (thx to AndreyT for pointing that out).

Problem

While executing below shown trigger code using ANT I am getting the error: org.postgresql.util.PSQLException: ERROR: unterminated quoted string at or near "' DECLARE timeout integer" Position: 57 I am able to sucessfully execute the below code through PGADmin (Provided by postgres) and command line utility "psql" and the trigger function is added but while executing through ANT it fails everytime ``` BEGIN TRANSACTION; CREATE OR REPLACE FUNCTION sweeper() RETURNS trigger as ' DECLARE timeout integer; BEGIN timeout = 30 * 24 * 60 * 60 ; DELETE FROM diagnosticdata WHERE current_timestamp - teststarttime > (timeout * ''1 sec''::interval); return NEW; END; ' LANGUAGE 'plpgsql'; -- Trigger: sweep on diagnosticdata CREATE TRIGGER sweep AFTER INSERT ON diagnosticdata FOR EACH ROW EXECUTE PROCEDURE sweeper(); END; ```

Original source