Thread: problems with dollar quoting

Hi all.  I hope someone can help me with a problem that's been bedeviling me for a couple of days now.  I'm on a development team that's using PostgreSQL and we're all supposed to be using the same scripts to initialize our databases.  Unfortunately the scripts are not working for me.  Here is the error I'm getting:

      [sql] Failed to execute:  CREATE FUNCTION unix_timestamp() RETURNS double precision AS $$ DECLARE myrec record; BEGIN select into myrec extract('epoch' from now()) as TS; return myrec.TS; END; $$ LANGUAGE plpgsql;

BUILD FAILED
C:\projects\svf_new\build.xml:453: java.sql.SQLException: ERROR: unterminated dollar-quoted string at or near "$$ DECLARE myrec record"

I'm really at a loss as to what might be causing this, and I've been searching on the Internet and on this mailing list without success.  First I ensured that all the SQL files were using the Windows EOL format (I'm running Windows XP).  Next I checked that "psql --version" and "select version();" return the same thing, and they do: I'm running 8.3.1.  I really don't know what else to do.  The other developers on my team are stumped as well, since nobody else is having this problem.  I spent about an hour working with the lead developer to try to figure this out before he had to leave to do something else.

I'm running a brand-new installation of PostgreSQL; this machine has never had PostgreSQL installed on it before.

Any help, pretty please? :)

Justin

Okay, after several more frustrating hours with no success, I finally realized that I could bypass the entire issue by changing all of the dollar-quoting in the SQL script to apostrophe-quoting.  It was a little tedious to change the entire script but it solved all of the quoting problems.

I'm kind of disappointed I didn't get any help from this list, but I'm posting my solution in case anybody else finds himself with the same problem.

Justin