Geek asks and answers

Creating a PostgreSQL Trigger Using JDBC

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

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

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

Update:

The Evolutions.scala code (line 219+) performs a simple breakdown on ;. It seems to be erroneous in the structure itself:

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

Any solutions?

+5
source share
1 answer

You can try the following.

    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 works for you.

+2

More articles:


All Articles