Cross-schema function call with PostGIS dependencies

Question

I have an issue where I am unable to make a cross-schema function call because of that function's use of PostGIS. I have both PostGIS and my function in the public schema, but I'm trying to call the function from my ORM which, by necessity, is pointed at another schema. I am able to reference the function by using SELECT * FROM public.functionName, but the function execution fails when it reaches PostGIS-relevant functions like ST_MakeLine. What is the best solution in this case, move PostGIS and my function out of public? Edit the function itself to call public.PostGISFunction? Something else entirely?

You probably need to make sure your search_path includes all the schemas you need to reference — John Powell, Nov 11 '18 at 20:39
related: https://gis.stackexchange.com/questions/292840/postgis-functions/292842#292842 — JGH, Nov 12 '18 at 12:13

score 1 · Accepted Answer · answered Nov 12 '18 at 12:12

The issue is with the search path. As suggested by @JohnPowell, you would need to make sure the user has public in its search path.

Alternatively, you could use add a configuration at the function level to add public to the search path of the function:

CREATE OR REPLACE FUNCTION public.mysuperfunction() RETURNS integer AS
$BODY$BEGIN

  RETURN 1;
END;$BODY$ LANGUAGE plpgsql
    -- Set path as Postgis needs public but user doesn't have access
    SET search_path = public;

Cross-schema function call with PostGIS dependencies

1 Answers1