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
Postgres describe DB via SQL query
Moderator: General Moderators
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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);
}