2

PostgreSQL 9.3

For the above PostgreSQL, PostGIS comes bundled with some 1050 functions. It seems these functions can only be loaded into ONE schema per database.

We have 3 three schemas (Production, Temporary, Public) in our database. We loaded those postgis functions to public.

We have set our search_path to (production, temporary, public).

Still it seems we when run out of, say, temporary we must dot the postgis function with "public.<function> (example: public.ST_AsText()). We were thinking that if our search path was set as above we would not have to "dot" the function. We interface with postgis via DevArt libraries from a vb .net application.

I'm pretty new to postgis and from my reading you can only have these functions in ONE schema; however, this seems a bit restrictive.

Also in pgAdmin when I open the tree view under our database there is an Extensions folder with two items. Opening the Extensions folder exposes a postgis and a plpgsql folder. If I select properties for the postgis folder and navigate to the Definition tab here is where the postgis functions are set to install. In our case public is set and here it seems only one schema can be set.

So is this a limitation one must live with, is this the best configuration, or is there some other configuration or setting which must be made to make the PostGIS functions global?

1

The PostGIS extension is installed in only one schema, public by default.

Any object that you create (Tables, functions, views etc) that consumes the PostGIS objects can be located in any schema. For them to access the PostGIS object, the schema containing PostGIS must be in the user search path.

Usually public is by default in every user search path.. you might have remove it.

Also some custom functions might have to declare the search_path to temporarily override the setting of the user running the function. For instance the user accessing objects via a Foreign Data Wrapper is restricted to the current schema by default (and pg_catalog), so an explicit SET search_path = public at the end of the function is required.

For example, you could have a trigger that extract the centroid of a polygon when it is updated. You would set the search path, allowing access to the public PostGIS functions.

CREATE OR REPLACE FUNCTION myschema.mytrigger() RETURNS trigger AS
$BODY$BEGIN
  NEW.xy = ST_Centroid(NEW.geom);
  RETURN NEW;
END;$BODY$ LANGUAGE plpgsql
    -- Set path as FDW access restricts to pg_catalog and Postgis needs public
SET search_path = public;
  • Thank you for the response! When we make a call via our middleware (DevArt) from a vb net application our adapter is set to either Temporary (development) or production (user community) so installing in either of these schemas is not an option as these functions must be centrally accessible (So I suppose public). These functions are not custom but part of the postgis package so I don't know about modifying each function and that seems dangerous modifying something that is part of the postgis baseline. – user9491577 Aug 15 '18 at 12:35
  • no, you don't have to touch the Postgis functions, only yours – JGH Aug 15 '18 at 12:37
  • As an example, ST_AsText is a postgis function, so I assume when you say "modify only yours' you are stating this is a COPY of a postgis function copied to our public schema. – user9491577 Aug 15 '18 at 12:45
  • @user9491577 no, it is a bad idea to duplicate PostGIS. See the updated answer for an example – JGH Aug 15 '18 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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