1

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.

2
  • Maybe you should use ogr_fdw instead of postgres_fdw. See crunchydata.com/blog/….
    – user30184
    Feb 27, 2023 at 12:25
  • @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. Mar 7, 2023 at 9:08

1 Answer 1

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;

2
  • 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! Mar 6, 2023 at 15:04
  • 1
    @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;)
    – JGH
    Mar 6, 2023 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.