Solved

SQL Executor calling postgres function

  • 8 February 2021
  • 3 replies
  • 21 views

Badge +6

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.

icon

Best answer by dbryantgeo 8 February 2021, 18:15

View original

3 replies

Userlevel 4

My first hunch would be to look at the login user configured in the SQLExecutor, and to check that it has the schema "land_registry" set as its default schema, or that "land_registry" appears in the schema search path. 

See e.g. https://www.postgresql.org/docs/9.3/ddl-schemas.html

SHOW search_path;
SET search_path TO land_registry,public;

 

 

Badge +6

My first hunch would be to look at the login user configured in the SQLExecutor, and to check that it has the schema "land_registry" set as its default schema, or that "land_registry" appears in the schema search path. 

See e.g. https://www.postgresql.org/docs/9.3/ddl-schemas.html

SHOW search_path;
SET search_path TO land_registry,public;

 

 

Hi David,

 

The "schemas for tables" is set to land_registry and the db connection used is a shared connection that is used for the database the schema sits on.

 

The function can also be run in the database itself, so it's almost like FME kicks off the function (when the land_registry schema is set on the function call in FME) but when it runs it "loses" the schema being referenced - which is why I then altered the function so that the schema was set against each statement in the function.

Badge +6

Hi David,

 

The "schemas for tables" is set to land_registry and the db connection used is a shared connection that is used for the database the schema sits on.

 

The function can also be run in the database itself, so it's almost like FME kicks off the function (when the land_registry schema is set on the function call in FME) but when it runs it "loses" the schema being referenced - which is why I then altered the function so that the schema was set against each statement in the function.

Just so that nobody else stumbles with this, what I needed to do was add the schema qualification to each table in each statement that the function carried out (there were a lot). It's like despite setting the land_registry schema everywhere I could, the function was being executed publically.

 

 

Reply