best way to get PKey and FKey from inside applications (permission pb)

Hi all,

I started using sqlalchemy (python ORM) that works really well but fails
detecting Primary and Foreign key other than for owner or
superusers. Sqlalchemy queries the information schema with the following query
that returns nothing if you are connected as a user with no particular
privileges:

SELECT
table_constraints.constraint_name ,
table_constraints.constraint_type ,
table_constraints.table_name ,
key_column_usage.table_schema ,
key_column_usage.table_name ,
key_column_usage.column_name ,
key_column_usage.constraint_name ,
constraint_column_usage.table_schema ,
constraint_column_usage.table_name ,
constraint_column_usage.column_name ,
constraint_column_usage.constraint_name

FROM information_schema.table_constraints
JOIN information_schema.constraint_column_usage ON
constraint_column_usage.constraint_name = table_constraints.constraint_name
JOIN information_schema.key_column_usage ON
key_column_usage.constraint_name = constraint_column_usage.constraint_name

WHERE table_constraints.table_name = 'my_table'
AND table_constraints.table_schema = 'public' ;

If you use '\d mytable' you get these information correctly so that there's
no reason to deny the same info from information_schema, correct?

Looking at how '\d' returns the information I always used a different query
(look here http://www.sqlalchemy.org/trac/ticket/71) that is not using
information_schema, but sqlalchemy would prefere to stick to the more
standard information_schema. What would you guys suggest in this case?

Thanks in advance
sandro
*:-)

--
Sandro Dentella *:-)
e-mail: sandro@e-den.it
http://www.tksql.org TkSQL Home page - My GPL work

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org