Page 1 of 1
Postgres describe DB via SQL query
Posted: Tue Feb 07, 2006 2:14 pm
by skehoe
Hey,
Hopefully this is an easy question. I'm trying to get a list of tables in a postgres DB via a PHP submitted query and having no luck. What would be the equivalent of doing a \d (describe) from the postgres prompt? Also, is there a way to get a list of indexes and sequences?
Thanks in advance for any help you can offer.
~Scott
Posted: Tue Feb 07, 2006 2:58 pm
by feyd
the following is from
phpPgAdmin for PostgreSQL 8:
Code: Select all
/**
* Return all tables in current database (and schema)
* @param $all True to fetch all tables, false for just in current schema
* @return All tables, sorted alphabetically
*/
function getTables($all = false) {
if ($all) {
// Exclude pg_catalog and information_schema tables
$sql = "SELECT schemaname AS nspname, tablename AS relname, tableowner AS relowner
FROM pg_catalog.pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
ORDER BY schemaname, tablename";
} else {
$sql = "SELECT c.relname, pg_catalog.pg_get_userbyid(c.relowner) AS relowner,
pg_catalog.obj_description(c.oid, 'pg_class') AS relcomment,
reltuples::integer,
(SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=c.reltablespace) AS tablespace
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND nspname='{$this->_schema}'
ORDER BY c.relname";
}
return $this->selectSet($sql);
}
Posted: Tue Feb 07, 2006 3:59 pm
by skehoe
I'll give that a try.
Thanks!
~Scott