Hi,
I've encountered a strange behavior of the JDBC driver 8.1.407 with PostgreSQL 8.1.4 (windows platform). I really
suspectthis is a bug inside the driver implementation, that's reason why I write this post.
Here is the use case :
I want to create a trigger with the JDBC API. This trigger function uses dollar-quoted escaped string literal. The
reasonfor this is that I use a search_path variable for the connected user, and I want postgres to automatically add
thecorrect db schema inside my trigger declaration. Here it is :
CREATE OR REPLACE FUNCTION procedure_insert_deleted_document() RETURNS TRIGGER AS $trigger_insert_deleted_document$
BEGIN INSERT INTO DELETED_DOCUMENT (DOC_ID, modificationDate, DOCVAULT_ID, guid) VALUES(OLD.DOC_ID, localtimestamp,
OLD.DOCVAULT_ID,OLD.guid); RETURN OLD; END; $trigger_insert_deleted_document$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_insert_deleted_document AFTER DELETE ON gch.DOCUMENT FOR EACH ROW EXECUTE PROCEDURE
procedure_insert_deleted_document();
The problem is that this statement is internally broke-up into too many SimpleQuery objects by the driver. In fact, the
$sign escape doesn't seem to be recognize and the above statement is splitted up in five (after each semicolon):
CREATE OR REPLACE FUNCTION procedure_insert_deleted_document() RETURNS TRIGGER AS $trigger_insert_deleted_document$
BEGIN INSERT INTO DELETED_DOCUMENT (DOC_ID, modificationDate, DOCVAULT_ID, guid) VALUES(OLD.DOC_ID, localtimestamp,
OLD.DOCVAULT_ID,OLD.guid)
RETURN OLD
END
$trigger_insert_deleted_document$ LANGUAGE plpgsql
CREATE TRIGGER trigger_insert_deleted_document AFTER DELETE ON gch.DOCUMENT FOR EACH ROW EXECUTE PROCEDURE
procedure_insert_deleted_document()
Of course, the execution of these statement fails after the first one with the following error :
ERROR: unterminated dollar-quoted string at or near "$trigger_insert_deleted_document$ BEGIN INSERT INTO
gch.DELETED_DOCUMENT(DOC_ID, modificationDate, DOCVAULT_ID, guid) VALUES(OLD.DOC_ID, localtimestamp, OLD.DOCVAULT_ID,
OLD.guid)"
On the contrary, if I fall back to standard quoted string, the statement below is this time split in two SimpleQuery
andsucceeds.
CREATE OR REPLACE FUNCTION procedure_insert_deleted_document() RETURNS TRIGGER AS ' BEGIN INSERT INTO
gch.DELETED_DOCUMENT(DOC_ID, modificationDate, DOCVAULT_ID, guid) VALUES(OLD.DOC_ID, localtimestamp, OLD.DOCVAULT_ID,
OLD.guid);RETURN OLD; END; ' LANGUAGE plpgsql;
CREATE TRIGGER trigger_insert_deleted_document AFTER DELETE ON gch.DOCUMENT FOR EACH ROW EXECUTE PROCEDURE
procedure_insert_deleted_document();
But this workaround doesn't suit me as it requires explicit schema prefix inside the trigger.
So my question is : Does this is a known issue of the JDBC driver or does it remind you something equivalent ?
(I can provide further details if needed)
Thanks for your answer
Regards,
Gregory
