Dear all
I am trying to compose a PostgreSQL function in FME using the SQLExecutor. My function installs correctly within pg admin, but since there is a dollar($) symbol included in the query, my function won't install correctly via FME and I receive the following error:
Error executing SQL command ('CREATE OR REPLACE FUNCTION gv.create_tables_from_foreign_schema(_foreign_schema character varying, _local_schema character varying)
RETURNS void AS
$BODY$'): 'ERROR: unterminated dollar-quoted string at or near "$BODY$"
The function looks like this:
CREATE OR REPLACE FUNCTION $(p4_p3_schema).create_tables_from_foreign_schema(_foreign_schema character varying, _local_schema character varying)
RETURNS void AS
$BODY$;
declare
selectrow record;
begin
for selectrow in
select 'CREATE TABLE ' || quote_ident(_local_schema) || '.' ||quote_ident(t.table_name) || ' AS SELECT * FROM ' || quote_ident(_foreign_schema) || '.' ||quote_ident(t.table_name) || '' AS qry
from (
SELECT table_name
FROM information_schema.tables
WHERE table_schema = _foreign_schema
)t
loop
execute selectrow.qry;
end loop;
end;
$BODY$;
LANGUAGE plpgsql ;
Has any of you experience either creating functions or triggers, from within FME using the SQLExcecutor and have you been able to circumvent this problem of using "$"? It's obvious that the $ symbol is reserved for FME parameters, so I wonder if either I could alter that FME setting or rewriting my SQL query?
