brushtyler/db_manager — Issue #3: error getting table from PG db
Author: brushtyler
Created: 2011-11-08 22:50:38+00:00
State: open

Hi,
opening the db-manager plugin and try-ing to open a postgis connection,
I see this python error:

```
An error has occured while executing Python code:

Traceback (most recent call last):
File "C:/Users/peri/.qgis/python/plugins\db_manager\db_model.py", line 399, in rowCount
self._refreshIndex( parent )
File "C:/Users/peri/.qgis/python/plugins\db_manager\db_model.py", line 443, in _refreshIndex
if item.populate():
File "C:/Users/peri/.qgis/python/plugins\db_manager\db_model.py", line 158, in populate
schemas = connection.database().schemas()
File "C:/Users/peri/.qgis/python/plugins\db_manager\db_plugins\plugin.py", line 222, in schemas
return map(lambda x: self.schemasFactory(x, self), schemas)
File "C:/Users/peri/.qgis/python/plugins\db_manager\db_plugins\plugin.py", line 222, in
return map(lambda x: self.schemasFactory(x, self), schemas)
File "C:/Users/peri/.qgis/python/plugins\db_manager\db_plugins\postgis\plugin.py", line 149, in schemasFactory
return PGSchema(row, db)
File "C:/Users/peri/.qgis/python/plugins\db_manager\db_plugins\postgis\plugin.py", line 161, in __init__
self.tableCount = len(self.tables())
File "C:/Users/peri/.qgis/python/plugins\db_manager\db_plugins\plugin.py", line 292, in tables
return self.database().tables(self)
File "C:/Users/peri/.qgis/python/plugins\db_manager\db_plugins\plugin.py", line 251, in tables
tables = self.connector.getTables(schema.name if schema else None)
File "C:/Users/peri/.qgis/python/plugins\db_manager\db_plugins\postgis\connector.py", line 179, in getTables
rasters = self.getRasterTables(schema)
File "C:/Users/peri/.qgis/python/plugins\db_manager\db_plugins\postgis\connector.py", line 338, in getRasterTables
self._execute(c, sql)
File "C:/Users/peri/.qgis/python/plugins\db_manager\db_plugins\postgis\connector.py", line 670, in _execute
raise DbError(e, sql)
DbError: missing FROM-clause entry for table "geo"
LINE 3: CASE WHEN geo.r_column IS NOT NULL THEN geo.r_column E...
^

Query:
SELECT
cla.relname, nsp.nspname, cla.relkind = 'v', pg_get_userbyid(relowner), cla.reltuples, cla.relpages,
CASE WHEN geo.r_column IS NOT NULL THEN geo.r_column ELSE att.attname END,
geo.pixel_types,
geo.scale_x,
geo.scale_y,
geo.out_db,
geo.srid

FROM pg_class AS cla
JOIN pg_namespace AS nsp ON
nsp.oid = cla.relnamespace

JOIN pg_attribute AS att ON
att.attrelid = cla.oid AND
att.atttypid = 'raster'::regtype OR
att.atttypid IN (SELECT oid FROM pg_type WHERE typbasetype='raster'::regtype )

WHERE cla.relkind IN ('v', 'r') AND nspname = 'dbt'
ORDER BY nsp.nspname, cla.relname, att.attname

Python version:
2.5.2 (r252:60911, Feb 21 2008, 13:11:45) [MSC v.1310 32 bit (Intel)]

QGIS version:
1.8.0-Trunk Trunk, b34f924

Python path: ['C:\\Users\\peri\\.qgis\\python\\plugins\\cswclient', 'C:/OSGeo4W/apps/qgis-dev/./python', 'C:/Users/peri/.qgis/python', 'C:/Users/peri/.qgis/python/plugins', 'C:/OSGeo4W/apps/qgis-dev/./python/plugins', 'C:\\OSGeo4W\\apps\\orfeotoolbox\\python', 'C:\\', 'C:\\OSGeo4W\\bin\\python25.zip', 'C:\\OSGeo4W\\apps\\Python25\\DLLs', 'C:\\OSGeo4W\\apps\\Python25\\lib', 'C:\\OSGeo4W\\apps\\Python25\\lib\\plat-win', 'C:\\OSGeo4W\\apps\\Python25\\lib\\lib-tk', 'C:\\OSGeo4W\\apps\\qgis-dev\\bin', 'C:\\OSGeo4W\\apps\\Python25', 'C:\\OSGeo4W\\apps\\Python25\\lib\\site-packages', 'C:\\OSGeo4W\\apps\\Python25\\lib\\site-packages\\win32', 'C:\\OSGeo4W\\apps\\Python25\\lib\\site-packages\\win32\\lib', 'C:\\OSGeo4W\\apps\\Python25\\lib\\site-packages\\Pythonwin', 'C:\\OSGeo4W\\apps\\Python25\\lib\\site-packages\\wx-2.8-msw-unicode', 'C:\\OSGeo4W\\apps\\qgis-dev\\python\\plugins\\fTools\\tools']
```

I use qgis-dev b34f924 on postgres 9.1.1 and ostgis 2.0 on windows7