Anonymous PostgreSQL PL/SQL in Informatica pre/post-sql

Question

I am trying to execute an anonymous PostgreSQL PL/SQL block in the pre or post SQL of a source qualifier in an Informatica mapping. PostgreSQL PL/SQL blocks require either $$ or $$ after the 'do' and the 'end' keywords.

A mapping with an anonymous PostgreSQL PL/SQL block in pre-sql like this:

do $$ 
begin
   raise notice '>>>>>>>>>>>>>>>>>>Test Proc';
end $$; 

Fails with this error:

Database driver error...
CMN_1022 [do $$
begin
   raise notice '>>>>>>>>>>>>>>>>>>Test Proc'
FnName: Execute Direct -- [Informatica][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: VERROR; unterminated dollar-quoted string at or near "$$
begin
   raise notice '>>>>>>>>>>>>>>>>>>Test Proc'"(Position 4; File scan.l; Line 1236; Routine scanner_yyerror; ), SQLSTATE [37000]
]

I tried an alternate form of this, putting text between the $ characters, but get the same error. I think it is interpreting the '$' as an Informatica mapping parameter, which it is not. I have tried escaping the characters, single-quote, double-quote, etc, but can't get it to work. I would prefer not to do it as a stored procedure if I don't have to.

Any ideas?

Answer 1

Look like this is the solution...single-quotes in place of the $$, and double single-quotes for anything in the block using single-quotes:

do '
begin raise notice ''>>>>>>>>>>>>>>>>>>Test Proc''; 
end '; 

Also referenced here ERROR: unterminated dollar-quoted string at or near "$BODY$