[postgis-tickets] [PostGIS] #3451: Schema-qualify table names to avoid search path confusion or "column does not exist" error

[PostGIS]

#3451: Schema-qualify table names to avoid search path confusion or "column does
not exist" error
-------------------------------------------------+-------------------------
 Reporter:  mst                                  |      Owner:  robe
     Type:  defect                               |     Status:  new
 Priority:  medium                               |  Milestone:  PostGIS
                                                 |  2.2.2
Component:  tiger geocoder                       |    Version:  2.2.x
 Keywords:  table name, column does not exist,   |
  schema                                         |
-------------------------------------------------+-------------------------
 At least some functions do not appear to schema-qualify table names and
 end up referencing the wrong table.  This results in an error stating a
 column does not exist:

 {{{
 ERROR:  column co.statefp does not exist
 LINE 2: ...cefp = p.placefp)  LEFT JOIN county co ON ('25' = co.statefp...
                                                              ^
 CONTEXT:  PL/pgSQL function geocode_address(norm_addy,integer,geometry)
 line 383 at FOR over EXECUTE statement
 PL/pgSQL function geocode(norm_addy,integer,geometry) line 14 at FOR over
 SELECT rows
 PL/pgSQL function geocode(character varying,integer,geometry) line 26 at
 RETURN QUERY
 }}}

 In this case, the county table existed in another schema in the search
 path.  Other table names used in the tiger and tiger_data schemas may also
 be in use (such as state, place, addr, zip_lookup, zip_state, etc. as well
 as their state-prefixed counterparts ma_place, id_addr, etc.).  Schema-
 qualifying all table names should avoid this issue.

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3451>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.