ERROR: unterminated quoted string at or near

Question

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;

Answer 1

I know this question was asked a long time ago but I had kind of the same issue with a Postgresql script (run from Jenkins) using Ant's SQL Task.

I tried to run this SQL (saved in a file named audit.sql):

DROP SCHEMA IF EXISTS audit CASCADE
;
CREATE SCHEMA IF NOT EXISTS audit AUTHORIZATION faktum
;
CREATE FUNCTION audit.extract_interval_trigger () 
RETURNS trigger AS $extractintervaltrigger$
BEGIN
        NEW."last_change_ts" := current_timestamp;
        NEW."last_change_by" := current_user;
        RETURN NEW;
END;
$extractintervaltrigger$ LANGUAGE plpgsql
;

but got the error "unterminated dollar-quoted string". No problem running it from pgAdmin.

I found out that it is not the driver that split the script at every ";" but rather Ant.

At http://grokbase.com/t/postgresql/pgsql-jdbc/06cjx3s3y0/ant-sql-tag-for-dollar-quoting I found the answer:

Ant eats double-$$ as part of its variable processing. You have to use $BODY$ (or similar) in the stored procs, and put the delimiter on its own line (with delimitertype="row"). Ant will cooperate then.

My Ant SQL script looks like this and it works:

<sql
    driver="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/jenkins"
    userid="user" password="*****"
    keepformat="true"
    autocommit="true"
    delimitertype="row"
    encoding="utf-8"
    src="audit.sql"
/>

Answer 2

I had the same problem with zeos and c++ builder. The solution in my case:
Change the property delimiter (usually ";") to another in the component (class) I used.

dm->ZSQLProcessor1->DelimiterType=sdGo;

Perhaps Ant have something similar.