Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.4k views
in Technique[技术] by (71.8m points)

postgresql - ERROR: unterminated quoted string at or near

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;
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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).


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...