Re: [Pypgsql-users] getting list of tables & column information

[Billy G. A. <Bil...@mu...>]

"Steven D. Arnold" wrote:
> Hello,
> 
> I've recently converted several applications from pygresql to pypgsql, but 
> I have one piece of code that is giving me trouble.  This module uses 
> pygresql's db.get_tables() command to obtain a list of all tables in a 
> postgres database.  This is similar information to what you'd get by 
> starting up postgres at the command line and using the command '\dt'.
> 
> Does anyone know how to get a listing of all tables in a given database via 
> pypgsql, either via a specialized command or a SQL equivalent?
> 
> Thanks!

You can get psql to tell you what it uses to get the list of tables:

$ psql -E -c '\dt'
********* QUERY *********
SELECT c.relname as "Name", 'table'::text as "Type", u.usename as "Owner"
FROM pg_class c, pg_user u
WHERE c.relowner = u.usesysid AND c.relkind = 'r'
  AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name", 'table'::text as "Type", NULL as "Owner"
FROM pg_class c
WHERE c.relkind = 'r'
  AND not exists (select 1 from pg_user where usesysid = c.relowner)
  AND c.relname !~ '^pg_'
ORDER BY "Name"
*************************

I hope this helps.
___________________________________________________________________________
____       | Billy G. Allie    | Domain....: Bil...@mu...
|  /|      | 7436 Hartwell     | MSN.......: B_G...@em...
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |