Postgresql trigger delimiter issue when creating test database via Maven

Question

I am currently working on a Java application which uses a PostgreSQL database. As part of the Maven build I test the database SQL scripts by dropping a test database, create it again and run all the SQL scripts.

On the Maven side I'm using the "sql-maven-plugin" plugin to execute these database commands and everything works nicely until I execute the create_triggers.sql script. This is because there are semicolon characters (;) inside the trigger functions. I've tried different things and my current (hacky) solution is to put set the <delimiter> of the SQL Maven Plugin to: -

<delimiter>;--</delimiter>

This specifies the delimiter as a semicolon immediately followed by a SQL comment. This means I can run the create_trigger.sql script in both pgAdmin and via maven. e.g.

create_scripts.sql

(note the ;-- after each statement)

DROP TRIGGER IF EXISTS refresh_updated_on_description ON my_table;--

/* Example trigger which updates `updated` field when `description` changes */

CREATE OR REPLACE FUNCTION refresh_updated()  
RETURNS TRIGGER AS $$
BEGIN
   IF (NEW.description != OLD.description
   THEN
      NEW.updatedn = now(); 
      RETURN NEW;
   ELSE
      RETURN NEW;
   END IF;

END;
$$ language 'plpgsql';--

CREATE TRIGGER refresh_updated_on_description BEFORE UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE refresh_updated();--

pom.xml

(note the <delimiter>;--</delimiter> element in the last <execution> section) ...

        <plugin>
            <groupId>org.codehaus.mojo</groupId>
            <artifactId>sql-maven-plugin</artifactId>
            <version>1.5</version>
            <dependencies>
                <dependency>
                    <groupId>org.postgresql</groupId>
                    <artifactId>postgresql</artifactId>
                    <version>9.4-1201-jdbc41</version>
                </dependency>
            </dependencies>
            <configuration>
                <driver>org.postgresql.Driver</driver>
                <username>${postgres.username}</username>
                <password>${postgres.password}</password>
                <skip>${maven.test.skip}</skip>
            </configuration>
            <executions>
                <execution>
                    <id>drop-and-create-test-database</id>
                    <phase>pre-integration-test</phase>
                    <goals><goal>execute</goal></goals>
                    <configuration>
                        <url>${postgresql.test.db.server}</url>
                        <autocommit>true</autocommit>
                        <sqlCommand>
                            drop database if exists myapp_test;
                            create database myapp_test;
                        </sqlCommand>
                    </configuration>
                </execution>
                <execution>
                    <id>run-database-scripts-on-test-db</id>
                    <phase>pre-integration-test</phase>
                    <goals><goal>execute</goal></goals>
                    <configuration>
                        <url>${postgresql.test.db.database}</url>
                        <srcFiles>
                            <srcFile>${basedir}/src/main/resources/sql/create_tables.sql</srcFile>
                        </srcFiles>
                    </configuration>
                </execution>
                <execution>
                    <id>run-database-create-complex-scripts-on-test-db</id>
                    <phase>pre-integration-test</phase>
                    <goals><goal>execute</goal></goals>
                    <configuration>
                        <url>${postgresql.test.db.database}</url>
                        <delimiter>;--</delimiter>
                        <srcFiles>
                            <srcFile>${basedir}/src/main/resources/sql/create_triggers.sql</srcFile>
                        </srcFiles>
                    </configuration>
                </execution>
            </executions>
        </plugin>

        ...

However, it feels like a hacky solution because I can only use the multi-line notation (/* some comment ... */) for declaring comments in the create_triggers.sql file . I also need to remember to include ;-- after each SQL statement as well in this file - otherwise the maven build will fail (but will run fine in pgAdmin).

Is there a better way to specify the delimiter inside the script?

Did you try to use "regular" single quotes instead of dollar-quoting? This does make maintaining the scripts a bit cumbersome, but should work even with Maven — , Jul 18 '16 at 11:42
Because the parser will (should) see only a string constant and will (should) not consider delimiters inside a string constant. — , Jul 18 '16 at 13:06
OK, might be worth posting an answer to further illustrate what you mean. — bobmarksie, Jul 18 '16 at 13:10
As of 2019, my preferred approach now is to use Flyway to handle database scripts, so much better in every way. — bobmarksie, Mar 03 '20 at 15:39

score 0 · Answer 1 · answered Mar 03 '20 at 11:03

Ref this question: Error when executing mvn sql:execute

The solution says to change the config, so that the delimiter must be on its own row:

<delimiterType>row</delimiterType>

Then you end your function like this:

  end if;
end;
$$ language 'plpgsql'
;

and the parser keeps reading until it finds the semi-colon on the end.

You'll have to modify all your other sql statements to add a carriage return before each semicolon.

Postgresql trigger delimiter issue when creating test database via Maven

create_scripts.sql

pom.xml

1 Answers1