I recently had to upgrade a PostGIS-enabled PostgreSQL 9.3 database to 9.4. In the process, it was decided that the server itself should be rebuilt and expanded. I accomplished the upgrade by dumping the data from the 9.3 installation (with
pg_dumpall -c), transferring the file over SCP, and loading it into the new database with psql.
Unfortunately that wasn’t enough - psql kicked back an error:
psql:dump.sql:7730: ERROR: function ST_AsLatLonText(public.geometry, unknown) does not exist LINE 1: SELECT ST_AsLatLonText($1, '') ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT ST_AsLatLonText($1, '') CONTEXT: SQL function "st_aslatlontext" during inlining
Long story short - if you run across this error, this is an issue which arises when function public.a calls function public.b without schema-qualifying it (calling ‘b’ instead of ‘public.b’). The workaround I’m using is to add the public schema to the end of the search path whenever it’s set in the dump. Running the dump through the following sed script should do it:
sed -E 's/(search_path = .+);/\1, public;/'