6

I'm quite new to postgresql and postgis so this error will probably due to some rookie mistake.

I want to run the spatial query ST_within. When I describe the query like this

SELECT nw.entity_id AS NavElem_id, nw.featuretype, nw.processingstatus AS NavElem_ProcessingStatus, nw.frc, nw.geometry AS NavElem_geom, aaa.processingstatus AS AA_processingstatus, aaa.networktype, aaa.geometry AS AA_geom, aaa.entity_id AS AA_id FROM sml.network nw, sml.aa_geo_att aaa WHERE ST_Within (nw.geometry, aaa.geometry);

It works fine, however I do not want to provide the schema name everytime (in this case sml.), I want to make it generic so that I can use it in an sql file.

When I try to set the schema path in a first query and the ST_within in second query

SET search_path TO sml;
SELECT nw.entity_id AS NavElem_id, nw.featuretype, nw.processingstatus AS NavElem_ProcessingStatus, nw.frc, nw.geometry AS NavElem_geom, aaa.processingstatus AS AA_processingstatus, aaa.networktype, aaa.geometry AS AA_geom, aaa.entity_id AS AA_id
FROM network nw, aa_geo_att aaa
WHERE ST_Within (nw.geometry, aaa.geometry);

I get the following error:

ERROR:  function st_within(public.geometry, public.geometry) does not exist
LINE 5: WHERE ST_Within (nw.geometry, aaa.geometry);
          ^
HINT:  No function matches the given name and argument types. You might need to add   explicit type casts.
********** Error **********

ERROR: function st_within(public.geometry, public.geometry) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 311

Even though I set the schema, ST within is looking in the public schema, which I don't want.

12

Your problem is that you have excluded the public schema from the search_path. All functions and types is in the public schema (by default)

So what you have to do is just include the public schema in the search_path

SET search_path TO sml, public;

means that the database will write to sml if you don't give any schema

SET search_path TO public, sml;

means that the database will write to public if no schema is given.

But in both cases both schemas will be searched for tables, functions and so on

  • 1
    So everytime I change the search_path to a schema, I also need to include the ,public? It works now, thank you – freakyfreak Jan 28 '15 at 10:21
  • 1
    @freakyfreak Yes. In fact, that mirrors the default search path used by PostgreSQL: $user, public. – Scro Jan 28 '15 at 13:17
  • 1
    @Freakyfreak: If you are not happy with the database search path, you can change it with: ALTER DATABASE SET search_path TO "$user", public, schema1, schema2, ...; Then PostgreSQL will search the paths in that order. You could also prefix the public schema on the function with something like public.ST_Within (though I personally don't do that). – LR1234567 Jan 28 '15 at 15:21
0

Piggybacking on a comment by LR1234567 on a previous answer, the proper syntax for the ALTER DATABASE command as of PostgreSQL 9.x is:

ALTER DATABASE my_database_name SET search_path = "$user", public, schema1, schema2;

See https://www.postgresql.org/docs/9.6/static/sql-alterdatabase.html

(I would have simply commented after his comment, but I don't have enough reputation.)

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.