pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
getting table names
2+ messages / 2 participants
[nested] [flat]

* getting table names
@ 2002-12-18 22:29 Mark Nelson <MCN@cc.usu.edu>
  2002-12-18 22:54 ` Re: getting table names scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Mark Nelson @ 2002-12-18 22:29 UTC (permalink / raw)
  To: pgsql-php

Hi,

I can't seem to find how to do this anywhere. I'm trying to query
the db and have it return the name of each of the tables in the
db. Something like \dt does in psql, but from PHP. Any ideas?

/mark




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: getting table names
  2002-12-18 22:29 getting table names Mark Nelson <MCN@cc.usu.edu>
@ 2002-12-18 22:54 ` scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 0 replies; 2+ messages in thread

From: scott.marlowe @ 2002-12-18 22:54 UTC (permalink / raw)
  To: Mark Nelson <MCN@cc.usu.edu>; +Cc: pgsql-php

On Wed, 18 Dec 2002, Mark Nelson wrote:

> Hi,
> 
> I can't seem to find how to do this anywhere. I'm trying to query
> the db and have it return the name of each of the tables in the
> db. Something like \dt does in psql, but from PHP. Any ideas?

Well, the quick and dirty trick is to run psql with the -E switch which 
will rEveal the sql psql is using to do it's dirty work.  In this case, 
7.2 and 7.3 are pretty similar.  For postgresql 7.2 it's:

SELECT c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
  u.usename as "Owner"
FROM pg_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid
WHERE c.relkind IN ('r','v','S','')
  AND c.relname !~ '^pg_'
ORDER BY 1;

For 7.3 it's:

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
  u.usename as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
      AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

note that you can probably trim a fair bit off of these queries to get 
just the table names.






^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2002-12-18 22:54 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-12-18 22:29 getting table names Mark Nelson <MCN@cc.usu.edu>
2002-12-18 22:54 ` scott.marlowe <scott.marlowe@ihs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox