How to create a function in PostgreSQL using evolutions in a Play structure?

Question

With 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 perform 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 am using the PostgreSQL driver version 9.1-901.jdbc4.

I looked through the postgres request 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 proper escaping. Has anyone else found a job for this? Do you think this is a JDBC issue, not a Play issue? Also, does anyone have Liquibase working with Play 2.1?

Also, just changing $$ to 'doesn't work either. In this case, we get another error, but we still cannot complete the evolution.

Edit: Added an example from a completely new game project. Download at: http://elijah.zupancic.name/files/play_evolution_problem.tar.gz

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

I just did an experiment where I am trying to completely insert the create sql function outside the playback frame. Example here: http://elijah.zupancic.name/files/PgCreateFunction.tar.gz <

It turns out that it is very reproducible.

EDIT: Turns out I can't play it in Java.

Answer 1

This is an artifact of how the game plays out evolution. Because it parses each statement at semicolons, it cannot process stored procedure definitions. The issue was resolved in Play 2.1, allowing you to specify embedded semicolons, doubling them. For example, https://github.com/playframework/Play20/pull/649 .

Using ;; I solved a similar problem for me using Play 2.1. I would suggest 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;