目錄
3.10 模式 一些用戶為了使某些模組的表看起來清晰,一般他們採用“模組名_表名”: Auth_user Auth_group Bbs_topic Bbs_message PostgreSQL不必這樣命名,可以使用Schema(模式)如: Auth.user Auth.group Bbs.topic Bbs.message 3.10.1 創建模式 CREATE SCHEMA your_schema; 例: CREATE SCHEMA btob; CREATE SCHEMA auction; 3.10.2 刪除模式 DROP SCHEMA your_schema; 刪除模式,並且同時刪除模式下的(表,視圖,觸發器,過程……) DROP SCHEMA your_schema CASCADE; 例: DROP SCHEMA btob CASCADE; DROP SCHEMA btob CASCADE; 3.10.3 模式搜索路徑 查看當前模式SHOW search_path ; netkiller=> SHOW search_path ; search_path -------------- $user,public (1 row) netkiller=> \dt List of relations Schema | Name | Type | Owner --------+-------------+-------+----------- public | company | table | netkiller public | group | table | netkiller public | groupmember | table | netkiller public | guestbook | table | netkiller public | prodorder | table | netkiller public | role | table | netkiller public | rolemember | table | netkiller public | system_log | table | netkiller public | templates | table | netkiller public | trust | table | netkiller public | user | table | netkiller public | user_log | table | netkiller public | userinfo | table | netkiller (13 rows) 如果不設置模式搜索路徑,“\dt”只顯示public模式下的表。 設置模式SET search_path TO public,btob,auction; netkiller=> SET search_path TO public,btob,auction; SET netkiller=> \dt List of relations Schema | Name | Type | Owner ---------+---------------+-------+----------- auction | messages | table | netkiller auction | product | table | netkiller auction | product_order | table | netkiller btob | directory | table | netkiller btob | trade | table | netkiller btob | trade_message | table | netkiller public | company | table | netkiller public | group | table | netkiller public | groupmember | table | netkiller public | guestbook | table | netkiller public | prodorder | table | netkiller public | role | table | netkiller public | rolemember | table | netkiller public | system_log | table | netkiller public | templates | table | netkiller public | trust | table | netkiller public | user | table | netkiller public | user_log | table | netkiller public | userinfo | table | netkiller (19 rows) netkiller=> -- ====================================================================== -- 'btob.directory' -- ====================================================================== Drop table btob.directory CASCADE; Create table btob.directory ( "id" Serial NOT NULL, "root_id" Integer NOT NULL Default 0, "name" Varchar(20)NOT NULL , "status" boolean Default 'true', "created" Timestamp Default current_timestamp, "modified" Timestamp Default current_timestamp, UNIQUE (id,root_id,name), PRIMARY KEY ("id") -- FOREIGN KEY (root_id) REFERENCES directory (id) ON DELETE CASCADE ); INSERT INTO btob.directory (id,root_id,name) VALUES (0,0,'/'); Alter table btob.directory add FOREIGN KEY (root_id) REFERENCES btob.directory (id) ON DELETE CASCADE; Create index "directory_index" on btob.directory using btree ("id","root_id","name");