Problems with semicolon trying to create a trigger function via jdbc - Mailing list pgsql-jdbc

Hi all,

I have some framework code that needs to dynamically generate a
function.  There seems to be a problem where the SQL gets truncated at
the first semicolon encountered in the function.  I have tried this
with a very simple function and duplicated it.

The test trigger function is as follows:
CREATE OR REPLACE FUNCTION test()
  RETURNS trigger AS
$BODY$
DECLARE
    foo integer;
BEGIN
    foo = 4;
    RAISE NOTICE 'Foo: %', foo;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION test() OWNER TO mcrtdbms;

The simple test code is:
String sql = "CREATE OR REPLACE FUNCTION
history.history_insert_trigger()     RETURNS trigger AS   $BODY$
DECLARE   foo integer;   BEGIN    foo = 4; RAISE NOTICE 'Foo: %', foo;
     END;   $BODY$     LANGUAGE 'plpgsql' VOLATILE; ";
        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();
        }

When I try to run this via my webapp I get the error:
org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted
string at or near "$BODY$   DECLARE   foo integer"
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1591)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1340)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
<snip>

So it seems to be truncating the SQL at the first semicolon it
encounters which, of course, borks the whole thing.  What is even
stranger in my quest to get this working is that the above code
actually WORKS when I run it through a JUnit test!!!  I have made no
progress in trying to figure out what is different between the unit
test and the running webapp.  At first I though it was my ORM so I
tried with the straight JDBC code used above and so eliminated that.
Now I am trying to determine if the Postgres JDBC driver is at fault.

Here is the rest of the details
 * JDBC version 8.3-605 JDBC 3
 * Postgres 8.3
 * JUnit 4
 * Application is built w/ Spring (but this manual query shouldn't be
affected by taht)
 * When the above query is run through the webapp, it is initially
triggered by a Quartz (scheduling api) trigger (which runs when the
webapp starts)

Any help at all would be appreciated!!  I am about to give up and
write a function which will do the job of creating the trigger for
me!!

Regards,
Collin