Re: Getting table metadata

I am not the original poster.

Here is what PSQL does to get the table name list:
connxdatasync=# \d
********* 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_'
UNION
SELECT c.relname as "Name", 'view'::text as "Type", u.usename as "Owner"
FROM pg_class c, pg_user u
WHERE c.relowner = u.usesysid AND c.relkind = 'v'
AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name", 'view'::text as "Type", NULL as "Owner"
FROM pg_class c
WHERE c.relkind = 'v'
AND not exists (select 1 from pg_user where usesysid = c.relowner)
AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name",
(CASE WHEN relkind = 'S' THEN 'sequence'::text ELSE 'index'::text END)
as "Type",
u.usename as "Owner"
FROM pg_class c, pg_user u
WHERE c.relowner = u.usesysid AND relkind in ('S')
AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name",
(CASE WHEN relkind = 'S' THEN 'sequence'::text ELSE 'index'::text END)
as "Type",
NULL as "Owner"
FROM pg_class c
WHERE not exists (select 1 from pg_user where usesysid = c.relowner) AND
relkind in ('S')
AND c.relname !~ '^pg_'
ORDER BY "Name"
*************************

Here is the sort of queries that would be made by PSQL to collect
information about a single table:

connxdatasync=# \d "LastActions"
********* QUERY *********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
FROM pg_class WHERE relname='LastActions'
*************************

********* QUERY *********
SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull,
a.atthasdef, a.attnum
FROM pg_class c, pg_attribute a
WHERE c.relname = 'LastActions'
AND a.attnum > 0 AND a.attrelid = c.oid
ORDER BY a.attnum
*************************

********* QUERY *********
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'LastActions' AND c.oid = i.indrelid AND i.indexrelid
= c2.oid
ORDER BY c2.relname
*************************

-----Original Message-----
From: ntinos(at)aueb(dot)gr [mailto:ntinos(at)aueb(dot)gr]
Sent: Wednesday, January 19, 2005 12:29 AM
To: Dann Corbit
Cc: Ken Tozier; PostgreSQL
Subject: Re: Getting table metadata

>
> SELECT * FROM pg_attribute WHERE attrelid=<the table oid>;
>
> The problem I'm running into however, is that given a table name,
there
> doesn't seem to be any way to get the table oid. Is there some
function
> or query that does this?

I think a way to get the table oid is:

select oid from pg_class where relname=<table_name>

Ntinos Katsaros