I have a SQL Executor that is meant to simply call a function on our PostGres database. The workbench is literally a Creator > SQL Executor > Logger
To call the function I am executing the below SQL;
do $$
begin
call apply_titles_update();
end
$$;
FME initially struggled to find the function so I added the schema, so the SQL now reads;
do $$
begin
call land_registry.apply_titles_update();
end
$$;
This function does a number of things, notable a lot of table drops and inserts, however when FME runs it now fails to find the tables;
Error executing SQL command ('do $$
begin
call land_registry.apply_titles_update();
end
$$;'): 'ERROR: relation "dataset_update_schedules" does not exist
LINE 4: ... from dataset_up...
^
QUE...'
A fatal error has occurred. Check the logfile above for details
I have then defined the schema to the actual function for each table, but still get the above message in FME.
The line above relates to this statement in the function;
select id,
filename,
update_type
into v_id,
v_filename,
v_update_type
from land_registry.dataset_update_schedules dus
where dus.update_status = 'Imported';
v_id, v_filename, v_update_type are all declared as part of the function.
I am unsure where now to turn so any advice is appreciated.