PostGIS functions

Question

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?

Answer 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;