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.

• user30184: Maybe you should use ogr_fdw instead of postgres_fdw. See crunchydata.com/blog/….
• Cao Minh Tu: @user30184 from GitHub page of ogr_fdw: This is only for testing, for best performance you should use postgres_fdw foreign data wrapper even when querying a PostGIS enabled database.

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;

• Cao Minh Tu: I tried putting "SET search_path = public;" at the end of the function as per your suggestion and the error was still raised. The problem was fixed when I put it at the beginning of the function. Please change your answer accordingly so that I can accept it. Thank you very much for your help!
• JGH: @CaoMinhTu :-) great that it is working. As per the create function definition, the order of some options doesn't matter, so it is fine to have the SET .. at various places, including at the very end, as long as it is not inside the sql body section of course. (PS: and I use it successfully at the end, i.e.... END;$BODY$ LANGUAGE plpgsql SET search_path = public;)