How do I create a function in PostgreSQL using evolutions in the Play framework?

Question

With the Play Framework 2.1

I have the following SQL defined in my evolution:

CREATE OR REPLACE FUNCTION idx(myArray anyarray, myElement anyelement) RETURNS int AS $$
 SELECT i FROM (
  SELECT generate_series(array_lower(myArray,1),array_upper(myArray,1))
 ) g(i)
 WHERE myArray[i] = anyElement
 LIMIT 1; $$ LANGUAGE sql IMMUTABLE;

When I execute the evolution, I get the following error:

We got the following error: ERROR: unterminated dollar-quoted string at or near 
"$$ SELECT i FROM ( SELECT generate_series(array_lower(myArray,1),
 array_upper(myArray,1)) ) g(i) WHERE myArray[i] = anyElement LIMIT 1" Position:
 87 [ERROR:0, SQLSTATE:42601], while trying to run this SQL script:

I'm using the PostgreSQL driver version 9.1-901.jdbc4.

I looked at the postgres query logs and found out that Play is trying to do the following:

LOG:  execute <unnamed>: insert into play_evolutions values($1, $2, $3, $4, $5, $6, $7)
PST DETAIL:  parameters: $1 = '1',
                         $2 = 'c834d463ebd9916b0a3388040300a0926514faef',
                         $3 = '2013-03-05 00:00:00',
                         $4 = '-- THE EVOLUTION UP STATEMENTS GO HERE',
                         $5 = '-- THE EVOLUTION DOWN STATEMENTS GO HERE',
                         $6 = 'applying_up',
                         $7 = ''

So, for some reason Play is trying to insert SQL into a text column without properly escaping. Has anyone else found a work around for this? Do you think this is a JDBC problem and not a Play problem? Also, has anyone got Liquibase working with Play 2.1?

Also, just changing the $$ to ' does not work either. In that case we get a different error, but we still can't execute the evolution.

Edit: I've added added an example from a brand new play project. Please download at: http://elijah.zupancic.name/files/play_evolution_problem.tar.gz

To get the example working you will need to create a new database as shown in the first comment on the evolution 1.sql. Then you will need to configure your conf/application.conf to connect to postgres on the correct port and with the correct user.

I've just did an experiment where I try to insert the create function sql totally outside of the play framework. The example is here: http://elijah.zupancic.name/files/PgCreateFunction.tar.gz<

It turns out that it is very reproducible.

EDIT: It turns out that I can't reproduce it in Java.

Answer 1

This is an artifact of how Play parses evolutions. Since it parses each statement on semicolons, it can't handle stored procedure definitions. The problem has been addressed in Play 2.1 by allowing you to specify embedded semicolons by doubling them. See https://github.com/playframework/Play20/pull/649, for instance.

Using ;; solved a similar problem for me, using Play 2.1. I'd suggest that you redefine your evolution as follows, and try again:

CREATE OR REPLACE FUNCTION idx(myArray anyarray, myElement anyelement) RETURNS int AS $$
 SELECT i FROM (
  SELECT generate_series(array_lower(myArray,1),array_upper(myArray,1))
 ) g(i)
 WHERE myArray[i] = anyElement
 LIMIT 1;; $$ LANGUAGE sql IMMUTABLE;