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

Answer 2

I had the same problem with the JDBC driver used by Liquibase.

It seems that the driver explodes each line ended by a semicolon and runs it as a separate SQL command. That is why the code below will be executed by the JDBC driver in the following sequence:

1. CREATE OR REPLACE FUNCTION test(text) RETURNS VOID AS ' DECLARE tmp text
2. BEGIN tmp := "test"
3. END;
4. ' LANGUAGE plpgsql

Of course, this is invalid SQL and causes the following error:

unterminated dollar-quoted string at or near ' DECLARE tmp text

To correct this, you need to use backslashes after each line ended with semicolon:

CREATE OR REPLACE FUNCTION test(text) 
RETURNS void AS ' DECLARE tmp text; \
BEGIN 
tmp := "test"; \
END;' LANGUAGE plpgsql;

Alternatively, you can place the whole definition in one line.

Answer 3

I am using HeidiSQL client and this was solved by placing DELIMITER // before CREATE OR REPLACE statement. There is a also a 'Send batch in one go' option in HeidiSQL that essentially achieves the same thing.

Answer 4

This error arises as an interaction between the particular client used to connect to the server and the form of the function. To illustrate:

The following code will run without casualty in Netbeans 7, Squirrel, DbSchema, PgAdmin3

CREATE OR REPLACE FUNCTION author.revision_number()
  RETURNS trigger AS
$BODY$
 begin
  new.rev := new.rev + 1;
  new.revised := current_timestamp;
  return new;
 end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Please note that the 'begin' statement comes immediately after the '$' quoted string.

The next code will halt all the above clients except PgAdmin3.

CREATE OR REPLACE FUNCTION author.word_count()
  RETURNS trigger AS 
$BODY$
   declare
    wordcount integer := 0; -- counter for words
    indexer integer := 1;  -- position in the whole string
    charac char(1);  -- the first character of the word
    prevcharac char(1);
   begin

    while indexer <= length(new.blab) loop
      charac := substring(new.blab,indexer,1); -- first character of string

      if indexer = 1 then
        prevcharac := ' '; -- absolute start of counting
      else
        prevcharac := substring(new.blab, indexer - 1, 1); -- indexer has increased
      end if;

     if prevcharac = ' ' and charac != ' ' then
       wordcount := wordcount + 1;
     end if;

     indexer := indexer + 1;
   end loop;
  new.words := wordcount;
  return new;
  end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

The crucial difference in the second example is the 'declare' section. The ploy of using back-slashes raises an error with PgAdmin3.

In summary I suggest trying different tools. Some tools even though they are supposed to be writing text files put invisible stuff into the text. Notoriously this occurs with the Unicode BOM which will halt any php file that tries to implement sessions or namespaces. Whilst this is no solution I hope it helps.

Answer 5

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.