Giovanni Azua Giovanni Azua - 12 days ago 6
Scala Question

We got the following error: null, while trying to run this SQL script

I get the above error coming from Play Evolutions while running activator run on a new Scala Play-Slick project. I'm using Postgres server version 9.3 and the latest JDBC driver 9.4. My

1.sql
file looks like this:

# schema

# --- !Ups

CREATE TABLE country (
id BIGINT,
name VARCHAR(100),
iso2 CHAR(2),
modified TIMESTAMP DEFAULT now(),
PRIMARY KEY (id)
);

CREATE OR REPLACE FUNCTION update_modified()
RETURNS TRIGGER AS $$
BEGIN
NEW.modified = now();
RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER trigger_country_modified BEFORE UPDATE ON country FOR EACH ROW EXECUTE PROCEDURE update_modified();

# --- !Downs

DROP TABLE country CASCADE;

DROP FUNCTION update_modified_column;


By trial and error I see that Evolutions doesn't understand well the native Postgres trigger function
update_modified
. How can I solve or circumvent this issue?

Answer

The Play evolution plugin splits your .sql files into a series of semicolon-delimited statements before executing them one-by-one against the database.

As you are using semicolons within the function update_modified() code, you have to escape it by entering it twice ;;. See below:

CREATE OR REPLACE FUNCTION update_modified()
RETURNS TRIGGER AS $$
BEGIN
    NEW.modified = now();;
    RETURN NEW;;
END;;
$$ language 'plpgsql';
Comments