포스트그레스 시스템 카탈로그 활용

포스트그레스 시스템 카탈로그 활용

작성자 : 주효식 (nogadax@kldp.org) 2000,08,06

다음은 시스템 카탈로그 활용예이다.

--Select database_name

 SELECT pg_database.datname , pg_user.usename      
  FROM   pg_database, pg_user	   
  WHERE  pg_database.datdba = pg_user.usesysid ;


--select system_relation

SELECT c.relname as Name, 'table'::text as Type, u.usename as Owner
	FROM pg_class c, pg_user u
	WHERE c.relowner = u.usesysid AND c.relkind = 'r'
	  AND not exists (select 1 from pg_views where viewname = c.relname)
		 AND c.relname ~ '^pg_';


--select normal_relation

SELECT c.relname as Name, 'table'::text as Type, u.usename as Owner
	FROM pg_class c, pg_user u
	WHERE c.relowner = u.usesysid AND c.relkind = 'r'
	  AND not exists (select 1 from pg_views where viewname = c.relname)
		 AND c.relname !~ '^pg_';



-- * Get general table info 

SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
  FROM pg_class WHERE relname='regist_tab';



-- * Get column info 

SELECT a.attname, t.typname,a.atttypmod-4 as Type_Length,t.typalign, 
          t.typlen ,a.attstorage,a.attnotnull, a.atthasdef, a.attnum
         
   FROM pg_class c, pg_attribute a, pg_type t
   WHERE c.relname = 'regist_tab'
   AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid
   ORDER BY a.attnum;



 --- 실행 결과 --- 



  datname  | usename  
-----------+----------
 postgres  | postgres
 betty     | postgres
 nogada    | postgres
 jini      | postgres
 winob     | postgres
 template1 | postgres
 registdb  | webdb
 test2     | speedall
 test1     | lockmind
(9 rows)



      name      | type  |  owner   
----------------+-------+----------
 pg_type        | table | postgres
 pg_attribute   | table | postgres
 pg_proc        | table | postgres
 pg_class       | table | postgres
 pg_group       | table | postgres
 pg_database    | table | postgres
 pg_attrdef     | table | postgres
 pg_relcheck    | table | postgres
 pg_trigger     | table | postgres
 pg_inherits    | table | postgres
 pg_index       | table | postgres
 pg_statistic   | table | postgres
 pg_operator    | table | postgres
 pg_opclass     | table | postgres
 pg_am          | table | postgres
 pg_amop        | table | postgres
 pg_amproc      | table | postgres
 pg_language    | table | postgres
 pg_aggregate   | table | postgres
 pg_ipl         | table | postgres
 pg_inheritproc | table | postgres
 pg_rewrite     | table | postgres
 pg_listener    | table | postgres
 pg_description | table | postgres
 pg_shadow      | table | postgres
(25 rows)

    
    
    name    | type  | owner 
------------+-------+-------
 regist_tab | table | webdb
(1 row)

 
 
 relhasindex | relkind | relchecks | reltriggers | relhasrules 
-------------+---------+-----------+-------------+-------------
 f           | r       |         0 |           0 | f
(1 row)

 
 
 attname | typname | type_length | typalign | typlen | attstorage | attnotnull | atthasdef | attnum 
---------+---------+-------------+----------+--------+------------+------------+-----------+--------
 a       | varchar |           2 | i        |     -1 | p          | f          | f         |      1  
 b       | varchar |           4 | i        |     -1 | p          | f          | f         |      2  
 c       | varchar |           6 | i        |     -1 | p          | f          | f         |      3
(3 rows)


----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
 
 
기타 시스템 카탈로그 SQL 문 



select usename from pg_user;

select tablename from pg_tables;

select relname,relhaspkey from pg_class;

select datname from pg_database;