Create PostgreSQL trigger using JDBC

Question

I am trying to create a PostgreSQL trigger in a Play2.0 database evolution script. The sql code is relatively easy and runs fine in pgAdminIII:

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

However, I get an error when running the evolution: ERROR: unterminated dollar-quoted string at or near "$$ BEGIN NEW.modified = now()". The SQL code seems to get truncated at the first semicolon encountered in the function. I am using the "9.1-901.jdbc4" JDBC driver for PostgreSQL.

Update:

The code in Evolutions.scala (line 219+) performs a simple split on the ;. Seems to be faulty in the framework itself:

// Execute script
s.sql.split(";").map(_.trim).foreach {
  case "" =>
  case statement => execute(statement)
}

Any solutions?

The code that creates the trigger is not my own since the sql script is a Play database evolution script. I guess the (Scala!) code can be found here. — Fynn
– Fynn, Commented May 5, 2012 at 10:08
Most probably that framework splits up your statement by ;. You need to tell it to run that String as a single statement, not a script. As I don't know that Play framework, I can't tell you how that has to be configured. — user330315
– user330315, Commented May 5, 2012 at 10:19

Bhavik Ambani · Accepted Answer · 2012-05-11 16:39:37Z

2

You can try the following thing.

    String sql = "CREATE OR REPLACE FUNCTION update_modified() RETURNS TRIGGER AS $$ " +
            "BEGIN " +
                "NEW.modified = now(); " +
                "RETURN NEW; " +
                "END; " +
            "$$ LANGUAGE 'plpgsql';";
    DataSource ds = getDataSource();
    try {
        Connection conn = ds.getConnection();
        conn.setAutoCommit(true);
        Statement st = conn.createStatement();
        st.executeUpdate(sql);
        st.close();
        conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

Hope this will work for you.

answered May 11, 2012 at 16:39

Bhavik Ambani

6,66515 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fynn Over a year ago

And where would be the right place for the code? It needs to be executed before the evolutions are evaluated. The Gloabal's onStart method seems to be the wrong place...

Collectives™ on Stack Overflow

Create PostgreSQL trigger using JDBC

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related