#107 Can't use VIEWs with postgres

[Henry S Thompson]

SQLObject v.1.2.1, Python 2.6, Windows 7 x64, Postgresql 9.1

The code in pgconnection.py for finding primary keys won't ever find anything for a VIEW. Not sure what the correct fix is (I don't understand postgres internals v. well), but the attached patch works for me. . .

[Henry S Thompson]

Candidate patch

[Oleg Broytman]

Instead of blindly copying idName wouldn't it be better to find out why PostgresConnection doesn't recognize pkeys on views? Try the following query replacing 'test' with the name of a VIEW:

SELECT pg_index.indisprimary,
pg_catalog.pg_get_indexdef(pg_index.indexrelid)
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
pg_catalog.pg_index AS pg_index
WHERE c.relname = 'test'
AND c.oid = pg_index.indrelid
AND pg_index.indexrelid = c2.oid
AND pg_index.indisprimary

It have to return something like

indisprimary | pg_get_indexdef
--------------+-----------------------------------------------------------
t | CREATE UNIQUE INDEX test_pkey ON test USING btree (my_id)
(1 row)

[Henry S Thompson]

Sorry not to be clearer -- what you've written is the existing code, which doesn't work for VIEWs. I don't understand how postgres lays out and uses its internal tables, so I can't offer any suggestions about how to modify that SELECT so that it works for VIEWs, sorry.

The proximate problem with that code is that although there is an entry in pg_class for a view, so there is an oid for it, that oid does _not_ appear in pg_index in the indrelid column (or in any other column). I'm afraid I don't know where to look next. . .

[Oleg Broytman]

Well, after reading Postgres' docs and looking into pg_catalog tables I see VIEWs don't have PRIMARY KEYs. Seems I have noting but accept your patch. Sorry it took so long and thank you for help!

Applied and committed in the revisions 4524-4526 (branches 1.2, 1.3 and the trunk). Will be in the next round of releases.