postgres migration error; unterminated dollar-quoted string

Question

Well this is abit strange, could anyone help me point out where this function may be wrong. I have a function similar to

CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (pwd = $2) INTO passed
        FROM    pwds
        WHERE   username = $1;

        RETURN passed;
END;

$$  LANGUAGE plpgsql

When i run it directly in the pgAdmin sql console, there are no errors but running it in a migration script using db-migration-maven-plugin i get the error.

Error executing: CREATE FUNCTION check_password(uname TEXT, pass TEXT) 
                 RETURNS BOOLEAN AS $$ DECLARE passed BOOLEAN
org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted 
                        string at or near "$$ DECLARE passed BOOLEAN"
Position: 74

Answer 1

The SQL generated by your migration scripts probably have some kind of $$ quotes in them that gets interpreted as a string somewhere.

A quick and dirty fix could be to change $$ to $func$ or even $check_password$, though there might be other functions further down that suffer the same problem.

The better, more long term approach will be to locate the offending $$.

Answer 2

@ivanorone: There is a bug filed for db-migration-maven-plugin: https://code.google.com/p/c5-db-migration/issues/detail?id=9 There is a patch included but looking at its source, it doesn't really fix the problem properly. Besides that, the project seems to be idling (last commit 2010).

There is another plugin, that I am trying to use instead, Flyway: http://flywaydb.org/ Switching to it was pretty easy and it works fine so far.