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.
Best Answer
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
means that the database will write to sml if you don't give any schema
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