2010年9月27日 星期一

PostgreSQL Schema 相關資訊取得方式

--列出所有資料表
SELECT * FROM information_schema.tables where table_schema='public'

--列出所有 Sequence
SELECT * FROM information_schema.sequences where sequence_schema='public'

--列出所有 Trigger
SELECT * FROM information_schema.routines where routine_schema='public' and data_type='trigger'

--列出角色可用資料表清單
select * from information_schema.role_table_grants where table_schema='public' and table_name in (SELECT table_name FROM information_schema.tables where table_schema='public')

--列出色角可用 Trigger
select * from information_schema.role_routine_grants where routine_schema='public' and routine_name in (SELECT routine_name FROM information_schema.routines where routine_schema='public' and data_type='trigger')

--列出可用角色清單
select * from information_schema.enabled_roles

--http://www.postgresql.org/docs/8.2/static/sql-grant.html
grant all on _data_history_sequence_seq to "postgres";

--http://www.postgresql.org/docs/8.2/static/sql-revoke.html
revoke all on _data_history_sequence_seq from "postgres";

1 則留言:

  1. --查詢帳號屬於哪個群組
    select * from information_schema.applicable_roles

    回覆刪除