search

jsor / doctrine-postgis

Spatial and Geographic Data with PostGIS and Doctrine.
MIT License
209 stars 50 forks source link

"Undefined function" error when SEARCH_PATH is different than the schema PostGIS is installed in #73

Open dece opened 1 month ago

dece commented 1 month ago

Hi,

If your PostgreSQL role has a different SEARCH_PATH than public or the one PostGIS has been installed in with the CREATE EXTENSION postgis SCHEMA xxx, retrieving entities from database will fail with the following error:

SQLSTATE[42883]: Undefined function: 7 ERROR:  function st_asewkt(public.geometry) does not exist
LINE 2: ...o.src AS src, o.cd_nom AS cd_nom, o.date AS date, ST_AsEWKT(...
                                                             ^
Hint: No function matches the given name and argument types.
You might need to add explicit type casts`

Altering the role to use public as search path makes the problem disappear. The problem can also be fixed by specifying the function schema directly in PostGISType::convertToPHPValueSQL.

-       return sprintf('ST_AsEWKT(%s)', $sqlExpr);
+       return sprintf('public.ST_AsEWKT(%s)', $sqlExpr);

I am not sure what the fix should be because I am not sure what are the guarantees that I left behind when I altered the search path for this role, but being able to specify the schema where PostGIS has been installed might be nice?