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 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 2

I was receiving the same error because I had my semicolon in a new line like this:

WHERE colA is NULL
;

Make sure they are in a single line as

WHERE colA is NULL;

Answer 3

This example worked for me with PostgreSQL 14.1 and HeidiSQL 9.4.0.5125

DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
    empname           text NOT NULL,
    salary            integer
);

DROP TABLE IF EXISTS EMP_AUDIT;
CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary integer
);

DELIMITER //
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $$
    BEGIN
        --
        -- Create a row in emp_audit to reflect the operation performed on emp,
        -- make use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS emp_audit ON emp;
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();

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 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 6

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.