Postgres describe DB via SQL query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
skehoe
Forum Commoner
Posts: 59
Joined: Sun Dec 22, 2002 5:57 am
Location: Denver

Postgres describe DB via SQL query

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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);
	}
User avatar
skehoe
Forum Commoner
Posts: 59
Joined: Sun Dec 22, 2002 5:57 am
Location: Denver

Post by skehoe »

I'll give that a try.

Thanks!

~Scott
Post Reply