PostgreSQL系统相关查询[1]

概述PG 系统查询脚本--查询用户进程及文本SELECT a.pid AS Id, a.usename AS User, a.client_addr AS Host, a.client_port AS Port, datname AS db, query AS Command, a.state AS Idle, l.mode as State, query_start AS Time, CASE

PG 系统查询脚本--查询用户进程及文本SELECT a.pID AS ID,a.usename AS User,a.clIEnt_addr AS Host,a.clIEnt_port AS Port,datname AS db,query AS Command,a.state AS IDle,l.mode as State,query_start AS Time,CASE WHEN c.relname IS NOT NulL THEN 'Locked Object: ' || c.relname ELSE 'Locked Transaction:' || l.virtualtransaction END AS Info FROM pg_stat_activity a left JOIN pg_locks l ON a.pID = l.pID left JOIN pg_class c ON l.relation = c.oID ORDER BY a.pID ASC,c.relname ASC--查询表[2015-03-03 17:38:35.668] [002232] [localhost] [PGsql]SELECT tablename,schemaname FROM pg_tables WHERE schemaname='public' ORDER BY schemaname,tablename--查询视图[2015-03-03 17:38:35.731] [002232] [localhost] [PGsql]SELECT vIEwname,schemaname FROM pg_vIEws WHERE schemaname='public' ORDER BY schemaname,vIEwname--查询所属视图及其定义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 JOIN pg_namespace n ON n.oID = c.relnamespace WHERE c.relkind = 'v'::"char" AND n.nspname = 'public' --查询架构名下表、表注释及表行数SELECT c.oID,c.relhasoIDs AS hasoIDs,n.nspname AS schemaname,c.relname AS tablename,pg_get_userbyID(c.relowner) AS tableowner,t.spcname AS "tablespace",c.relhasindex AS hasindexes,c.relhasrules AS hasrules,c.relhastriggers AS hastriggers,c.reltuples,((SELECT count(*) FROM pg_inherits WHERE inhparent = c.oID) > 0) AS inhtable,i2.relname AS inhtablename,c.reloptions AS param FROM pg_class c left JOIN pg_namespace n ON n.oID = c.relnamespace left JOIN pg_tablespace t ON t.oID = c.reltablespace left JOIN (pg_inherits i INNER JOIN pg_class c2 ON i.inhparent = c2.oID) i2 ON i2.inhrelID = c.oID WHERE (c.relkind = 'r'::"char") AND n.nspname = 'public'order by reltuples--查询数据库及所属用户和字符集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 JOIN pg_tablespace t ON d.dattablespace=t.oID

--EOF

总结

以上是内存溢出为你收集整理的PostgreSQL系统相关查询[1]全部内容，希望文章能够帮你解决PostgreSQL系统相关查询[1]所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。