Skip to main content
Solved

SQL Executor calling postgres function

  • February 8, 2021
  • 3 replies
  • 150 views

dbryantgeo
Contributor
Forum|alt.badge.img+7

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.

Best answer by dbryantgeo

dbryantgeo wrote:

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.

 

 

View original
Did this help you find an answer to your question?

3 replies

david_r
Evangelist
  • February 8, 2021

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;

 

 


dbryantgeo
Contributor
Forum|alt.badge.img+7
  • Author
  • Contributor
  • February 8, 2021
david_r wrote:

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.


dbryantgeo
Contributor
Forum|alt.badge.img+7
  • Author
  • Contributor
  • Best Answer
  • February 8, 2021
dbryantgeo wrote:

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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings