Обсуждение: Getting table metadata
<fontfamily><param>Arial</param>I recently stumbled upon the system catalog functions here "http://www.postgresql.org/docs/7.4/static/catalogs.html" and see that it's easy to get a list of all databases and relatively easy to get a list of tables, but there doesn't seem to be any built in method for retrieving a table definition. The best I could come up with would be to do a select something like this: 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? Better yet, is there an easier way to get at this metadata? Thanks for any help, Ken</fontfamily> I recently stumbled upon the system catalog functions here "http://www.postgresql.org/docs/7.4/static/catalogs.html" and see that it's easy to get a list of all databases and relatively easy to get a list of tables, but there doesn't seem to be any built in method for retrieving a table definition. The best I could come up with would be to do a select something like this: 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? Better yet, is there an easier way to get at this metadata? Thanks for any help, Ken
Look at the SQL in the PG Admin III project source code base.
It’s non-trivial SQL to collect all the information about a table.
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ken Tozier
Sent: Tuesday, January 18, 2005 9:15 PM
To: PostgreSQL
Subject: [GENERAL] Getting table metadata
I recently stumbled upon the system catalog functions here "http://www.postgresql.org/docs/7.4/static/catalogs.html" and see that it's easy to get a list of all databases and relatively easy to get a list of tables, but there doesn't seem to be any built in method for retrieving a table definition. The best I could come up with would be to do a select something like this:
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? Better yet, is there an easier way to get at this metadata?
Thanks for any help,
Ken
On Wed, Jan 19, 2005 at 12:14:57AM -0500, Ken Tozier wrote: > I recently stumbled upon the system catalog functions here > "http://www.postgresql.org/docs/7.4/static/catalogs.html" and see that > it's easy to get a list of all databases and relatively easy to get a > list of tables, but there doesn't seem to be any built in method for > retrieving a table definition. See also "The Information Schema" if you're using 7.4 or later. > The best I could come up with would be to do a select something > like this: > > 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. See "Object Identifier Types" in the "Data Types" chapter. SELECT * FROM pg_attribute WHERE attrelid = 'tablename'::regclass; > Is there some function or query that does this? Better yet, is there > an easier way to get at this metadata? If you run "psql -E" you can see the queries that psql makes when you issue commands like "\d tablename". As you can see, it takes a lot of information from the system catalogs to generate a description of a table. The Information Schema abstracts these queries through views, so querying them might be the easiest way if they provide what you need. See in particular information_schema.columns. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael, > See "Object Identifier Types" in the "Data Types" chapter. > > SELECT * FROM pg_attribute WHERE attrelid = 'tablename'::regclass; Thanks. That worked like a champ! Ken
> > 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
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@aueb.gr [mailto:ntinos@aueb.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