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

sandro -

SQL2003 fixes these issues with information_schema but it seems that
postgres has not caught up yet:
http://groups.google.com/group/pgsql.interfaces/browse_thread/thread/9f19995849b3cdf4/c20b81bf8cf183af?lnk=st&q=information+schema&rnum=9&hl=en#c20b81bf8cf183af

although i am running only 8.0.4 here. if 8.1 still has this problem
(and its documentation for information_schema still seems to indicate
the views show constraints "owned" by the current user rather than
"accessible"), then we might as well use PG's system tables until this
is resolved. can you submit a patch to postgres.py that uses the PG
system tables ?

Sandro Dentella wrote:
> 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(at)e-den(dot)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