Error inserting data into PostGIS/PostgreSQL database through postgres_fdw Foreign Data Wrapper

Question

I'm trying to insert data (a line geometry) into a foreign table which was mapped from a view in a PostGIS/PostgreSQL database. That view in turn, check if the the line is inside a polygon before inserting into a table. Every time I insert data, PostgreSQL generate an error:

CRITICAL    Layer my_line_layer : PostGIS error while adding features: ERROR: function st_contains(public.geometry, public.geometry) does not exist
             HINT: No function matches the given name and argument types. You might need to add explicit type casts.
              CONTEXT: PL/pgSQL function my_schema.my_line_layer_dml() line 4 at IF
              remote SQL command: INSERT INTO my_schema.my_line_layer(id, geom, name) VALUES ($1, $2, $3) RETURNING id

I checked that function ST_Contains exists in public schema, I can connect to the remote server and insert data directly into the same view without any problem.

Answer 1

The search path of the user used for the remote connection is restricted to the current schema. The trigger or view-function must therefore add public to its path (or whichever schema contains postgis). At the very end of the function, add SET search_path = public;