PostgreSQL system related queries [1]

PG System Query Script--query user process and text select A.pid as Id, a.usename as user, a.client_addr as Host, a.client_port as Port, datname as DB, Qu Ery as Command, a.state as Idle, L.mode as state, Query_start as time, case when C.relname was not NULL and then ' Locked Object : ' || C.relname ELSE ' Locked Transaction: ' | | L.virtualtransaction END as Info from Pg_stat_activity a left joins pg_locks l on a.pid = L.pid left joins Pg_class C on L.R Elation = C.oid ORDER by A.pid ASC, c.relname asc--query table [2015-03-03 17:38:35.668] [002232] [localhost] [pgsql]select Tablena Me, schemaname from Pg_tables WHERE schemaname= "public" ORDER by SchemaName, tablename--query view [2015-03-03 17:38:35.731] [00 2232] [localhost] [pgsql]select viewname, schemaname from pg_views WHERE schemaname= ' public ' ORDER by SchemaName, viewname --Query owning view and its definition select C.oid, C.relname, C.relacl, Pg_get_userbyid (C.relowner) as Viewowner, Pg_get_viewdef (c.oid) as Definition, obj_description (c.oid), n.nspname from Pg_class C left joins Pg_namespace n on n.oid = C.relnamesPace WHERE C.relkind = ' V ':: ' char ' and n.nspname = ' public '--Query Schema name table, table comment, and table row number select C.oid, Obj_description (c.oid), C.RELH Asoids as Hasoids, n.nspname as SchemaName, c.relname as TableName, Pg_get_userbyid (C.relowner) as Tableowner, T.spcname A S "Tablespace", C.relhasindex as Hasindexes, c.relhasrules as Hasrules, c.relhastriggers as Hastriggers, C.relacl, C.reltu Ples, ((SELECT count (*) from pg_inherits WHERE inhparent = c.oid) > 0) as inhtable, i2.relname as Inhtablename, C.relop tions as param from pg_class C left join Pg_namespace n in n.oid = C.relnamespace left join Pg_tablespace t on t.oid = C.R Eltablespace left join (pg_inherits i INNER joins Pg_class c2 on i.inhparent = c2.oid) i2 on i2.inhrelid = c.oid WHERE (C.R Elkind = ' R ':: ' char ') and n.nspname = ' public ' ORDER by reltuples--querying the database and its owning user and character set select D.datname, D.oid, Pg_get_userbyid ( D.DATDBA) as owner, Shobj_description (d.oid, ' pg_database ') as comment, T.spcname, D.datacl, D.datlastsysoid, d.encoding , Pg_encoding_to_char (D.encoding) as Encodingname from Pg_database D left joins Pg_tablespace T on d.dattablespace=t.oid  

--eof

PostgreSQL system related queries [1]