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