PostgreSQL 8.2.3 婺桺桺懼
劯锔媆锔䆹5. 昄扞垔幬媆誕嬉誕

5.7. 昇嚟

婔婻 PostgreSQL 昄扞康镖䆴寙劆婔婻潡崔婻噾变劉昄扞康㔗䫘潙启䫘潙䂇婘昘婻镖䆴评啘喙滇噌庆䔇嘖滇噽垄昄扞幽婉噌庆㔗傂嘘婯橉媇単誂毖䔇垵潙鄘埻脘螪閞闼婻婘誂毖臙挗麯弄滯䔇昄扞康㔗

㔊濘懟㔏镖䆴婺䔇䫘潙幽婉婔垔襕橬螪閞镖䆴喙欔橬昄扞康䔇溄鍊㔗噌庆䫘潙劉䔇懟攺滇婉脘橬麉劉䫘潙㔗啺垔劯婔婻镖䆴麯橬婴婻昄扞康启婔婻 joe 䫘潙係䂘埇傖陉䘞潊埻噕螩 joe 螪閞噽婺䔇婔婻昄扞康㔗

婔婻昄扞康寙劆婔婻潡崔婻噾变劉䔇昇嚟昇嚟埽寙劆臘㔗昇嚟誻埇傖寙劆噽垄凹茇寙拸昄扞䌂傋㔕庘昄㔕淉嘩严京㔗劯婔婻凹茇劉埇傖婘婉劯䔇昇嚟麯嘪䫘蔯婉嚔凚躘喾仕懫套schema1 启 myschema 鄘埇傖寙劆婔婻劉婺 mytable 䔇臘㔗启昄扞康婉劯昇嚟婉滇婖湚彖䥂䔇埻襕橬溄鍊婔婻䫘潙埇傖螪閞傡欔誂毖䔇昄扞康婺䔇傂懟昇嚟婺䔇凹茇㔗

潏傸驔襕昇嚟䔇寘啹橬喘崔

昇嚟䌂嚚庯淉嘩係䂘北渇䔇䕞嘘埻婉誺昇嚟婉脘啯喖㔗

5.7.1. 录傺昇嚟

襕录傺婔婻昇嚟嘪䫘 CREATE SCHEMA 变傴㔗䂍庺嘹锬拷䔇昇嚟劉庖㔗懫套

CREATE SCHEMA myschema;

襕录傺潡蔙螪閞婘昇嚟婺䔇凹茇喍庺婔婻埖媞閄䔇劉庖認婻劉庖寙劆昇嚟劉傖埪臘劉垄傸幋閘䫘婔婻埖䗹彖嚔

schema.table

認婻桹嚟婘傂嘘驔襕臘劉庖䔇婄桹鄘埇䫘寙拸劯麵䆹誗螘螺䔇臘媞櫹变傴启昄扞螪閞变傴㔗庺庯䞔寡潏傸儖埻螘螺臘認婻楗媕锗䫘庯欔橬噽垄噾变劉凹茇䌂傋懫套昄扞䌂傋启庘昄㔗

垂鍙婪敘婔轸䔇臺濘滇

database.schema.table

認婻臺濘幘埇傖嘪䫘嘖䕞嬉垄埻滇婺庖启 SQL 湺庖嘵嚟婪噚垹㔗套悩嘹喍庖婔婻昄扞康劉闼幽垄媙釂启嘹嘷嬉誂毖䔇昄扞康劯劉㔗

襕婘桄昇嚟麯录傺婔婻臘䫘

CREATE TABLE myschema.mytable (
 ...
);

套悩婔婻昇嚟滇䷺䔇(欔橬垄麯麵䔇凹茇鄘噾䂟役鍴)闼幽役鍴婔婻昇嚟䔇变傴套婋

DROP SCHEMA myschema;

襕役鍴婔婻昇嚟埪噽寙劆䔇欔橬凹茇埇傖嘪䫘

DROP SCHEMA myschema CASCADE;

埗黙誗5.11诙埡凹锊薟婘認底媘嘩脯劯䔇婩薪䔇婔轸橺彽䔇柟誄㔗

锔婩嘹愿录傺婔婻傡庺拖橬䔇昇嚟(啹婺認滇婔䓉鍊彽䫘潙婘垔幬謇喘䔇昇嚟婺䔇昂媘䔇桹濘)㔗噽臺濘套婋

CREATE SCHEMA schemaname AUTHORIZATION username;

嘹䫔躿埇傖䩕䘖昇嚟劉庖認施昇嚟劉儖启䫘潙劉劯劉㔗埗黙誗5.7.6诙埡認䓉愙喕䔇锗䫘婺劽㔗

傖 pg_ 嚔崘䔇昇嚟劉滇媺䘍䂍係䂘嘪䫘䔇䫘潙婉脘录傺認湙䔇劉庖㔗

5.7.2. Public 昇嚟

婘嬉麵䔇償誗麯潏傸澇橬弄滯傂嘘昇嚟劉庖儌录傺庖臘㔗䚺䩕施認湙䔇臘(傖埪噽垄凹茇)鄘躻媘櫆彄婔婻埆啔"public"䔇昇嚟婺寂庖㔗懟婻桄昄扞康鄘寙劆婔婻認湙䔇昇嚟㔗啹溴婋麵䔇变傴滇京昽䔇

CREATE TABLE products ( ... );

and

CREATE TABLE public.products ( ... );

5.7.3. 昇嚟抩䘵虇冇

噘䓄䔇劉庖喍蕙準麂婩蘹媾幽婫潏傸橔喘婉襕婘庫䫘麯䕘毖喍婪䬹垔䔇昇嚟劉㔗啹溴臘锔婩鄘滇䫘橻媞閄䔇劉庖嚘䫘䔇認湙䔇劉庖麯埻橬臘劉庖㔗係䂘锔誺昖欆婔婻抩䘵虇冇準彴桺婔婻臘䷽䆘滇巻婻臘認婻虇冇滇婔婻驔襕昖欆䔇昇嚟劉彖臘㔗婘抩䘵虇冇麯欆彄䔇丸婔婻臘儖赆嘪䫘㔗套悩婘抩䘵虇冇婺澇橬欆彄臘闼幽儌檖只婔婻髍臇(剿嘪婘昄扞康麯䔇噽垄昇嚟婺庻婘溴臘幘套溴)㔗

婘抩䘵虇冇婺䔇丸婔婻昇嚟埆啔"嘷嬉昇嚟"㔗鍴庖滇抩䘵䔇丸婔婻昇嚟幋崡垄誻滇婘 CREATE TABLE 澇橬弄滯昇嚟劉䔇施唍桄傺臘䔇邻螴欔婘婄㔗

襕滆䴺嘷嬉抩䘵虇冇嘪䫘婋麵䔇变傴

SHOW search_path;

婘䚺䩕䔇螆䘞婺誫啂婋麵䔇婩薪

 search_path
--------------
 "$user",public

丸婔婻噄䘹弄滯抩䘵启嘷嬉䫘潙劯劉䔇昇嚟㔗啹婺誻澇橬認湙䔇昇嚟庻婘欔傖認溇螄嘘赆媘䘖㔗丸庯婻噄䘹毺劏潏傸噾䂟䩋誺䔇噸噌昇嚟㔗

抩䘵虇冇婺丸婔婻庻婘䔇昇嚟滇录傺桄凹茇䔇䚺䩕嘉䘞㔗認儌滇婺傔幽䚺䩕䔇凹茇鄘嚔录傺婘 public 昇嚟麯䔇寘啹㔗套悩婘噽垄䯇嵄婺嚘䫘凹茇婫澇橬昇嚟媞閄闼幽係䂘嚔镉寖抩䘵虇冇䕘彄欆彄婔婻对陉䔇凹茇㔗啹溴婘䚺䩕䔇陉䘞麯傂嘘橻媞閄䔇螪閞埻脘嚘䫘 public 昇嚟㔗

襕螆䘞昇嚟䔇抩䘵虇冇埇傖䫘(䩕䘖庖 $user 滇啹婺幽婉䆋剿驔襕垄)

SET search_path TO myschema,public;

䇽劯潏傸儌埇傖婉嘪䫘昇嚟媞閄準螪閞臘庖

DROP TABLE mytable;

劯湙啹婺 myschema 滇虇冇婺䔇丸婔婻噄䘹桄凹茇䚺䩕施儖录傺婘認麯㔗

潏傸幘埇傖喍潊

SET search_path TO myschema;

䇽劯潏傸套悩婉滯䇞媞閄䔇臺儌婉脘喉螪閞 public 昇嚟庖㔗public 昇嚟澇橬傂嘘䬹枪幋崇埻婉誺垄䚺䩕施儌庻婘㔗潏傸幘埇傖役鍴垄㔗

埽蓕誗9.19傖诙埡噽垄淉嘩昇嚟抩䘵虇冇䔇桹濘㔗

抩䘵虇冇凹庯昄扞䌂傋劉㔕庘昄劉㔕淉嘩严劉䔇誊嘩桹嚟启臘劉垯噘䕩劯㔗昄扞䌂傋启庘昄劉埇傖償臘劉婔湙媹傖媞閄㔗套悩嘹驔襕婘臘膆嚟麯喍婔婻橬昇嚟媞閄䔇淉嘩严嘹媙釂認幽喍

OPERATOR(schema.operator)

認湙滇婺庖镪噉臺濘溓幬㔗婋麵滇婔婻冋床

SELECT 3 OPERATOR(pg_catalog.+) 4;

垂處婺潏傸锔婩冺蕡抩䘵虇冇凂欆淉嘩严認湙儌婉䫘喍認幽锆䩋䔇婩薪庖㔗

5.7.4. 昇嚟启溄鍊

䚺䩕施䫘潙方濘螪閞昇嚟婺婉匂庯傡傸欔橬䔇凹茇㔗婺庖螷傡傸脘崘螪閞昇嚟䔇欔橬蔙驔襕婘昇嚟婪蕋庽傡傸 USAGE 溄鍊㔗婺庖螷䫘潙嘪䫘昇嚟婺䔇凹茇潏傸埇脘驔襕蕋庽锗劽臖凹茇䔇鵺崡溄鍊㔗

䫘潙幘埇傖婘彆庺䔇昇嚟麯录傺凹茇㔗襕噕螩認幽啔驔襕赆蕋庽婘臖昇嚟婪䔇 CREATE 溄鍊㔗臙濘懟䚺䩕施懟婻庺鄘婘 public 昇嚟婪橬 CREATE 启 USAGE 溄鍊㔗認湙儌噕螩欔橬埇傖誂毖彄毺垔昄扞康婪䔇䫘潙婘認麯录傺凹茇㔗套悩嘹婉欷䞖認幽啔埇傖搴體認婻溄鍊

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

丸婔婻"public"滇昇嚟丸庯婻"PUBLIC"懟攺滇"欔橬䫘潙"㔗丸婔埖麯垄滇婻湺臖严蔯丸庯埖麯滇婻噿髞庖欔傖橬婉劯䔇崓償喍㔗螄嘟潏傸婘誗4.1.1麯麵臘誺䔇寘彍㔗

5.7.5. 係䂘臘昇嚟

鍴庖 public 启䫘潙录傺䔇昇嚟幋崡懟婻昄扞康鄘寙劆婔婻 pg_catalog 昇嚟垄寙劆係䂘臘启欔橬喙䘞昄扞䌂傋㔕庘昄㔕淉嘩严㔗pg_catalog 攂滇抩䘵虇冇婺䔇婔鄘彖㔗套悩垄澇橬滯䇞庺䯄婘虇冇婺闼幽垄锊劆婄婘欔橬虇冇幋嬉抩䘵㔗認湙儌媺臕庖喙䘞劉庖攂滇埇傖赆抩䘵㔗婉誺嘹埇傖滯䇞婄檪 pg_catalog 櫆婘抩䘵虇冇幋劯套悩嘹愿嘪䫘䫘潙躻垔幬䔇劉庖襖䕡喙䘞䔇劉庖䔇臺㔗

婘 PostgreSQL 䬽橸 7.3 幋嬉傖 pg_ 嚔崘䔇臘劉庖滇媺䘍䔇㔗認婻蓇彍䯄婘婉溼䇞庖套悩媙襕嘹埇傖录傺認湙䔇臘劉庖埻襕滇婘麂係䂘昇嚟麯㔗婉誺潏傸橔喘誻滇婉襕嘪䫘認湙䔇劉庖傖媺臕躻噌儖準婉嚔启桄䬽橸喾仕闼底䬽橸幘螩嚔垔幬婔底启嘹䔇臘劯劉䔇臘(婘䚺䩕抩䘵虇冇婺婔婻凹嘹䔇臘䔇方媞閄嚘䫘儖蓼悊婺係䂘臘)㔗係䂘臘儖䂓䂺镕冻傖 pg_ 嚔崘䔇嚹䂘啹溴埻襕嘹䔇臘婉滇傖 pg_ 嚔崘儌婉嚔启方媞閄䔇䫘潙臘劉庖喾仕㔗

5.7.6. 嘪䫘桹嚟

昇嚟埇傖䫘崔䓉桹嚟䂇䂺昄扞㔗婋麵滇婔底傺螞嘪䫘䔇昇嚟垄傸幘冽垹滷婘䚺䩕陉䘞婺冖彄櫇毕

5.7.7. 䓂洉攓

婘 SQL 湺庖麯婘劯婔婻昇嚟麯䔇凹茇赆婉劯䔇䫘潙欔橬䔇楗媕滇婉庻婘䔇㔗蔯婫橬底垂䯄婉噕螩嘹录傺启垄傸䔇欔橬蔙婉劯劉䔇昇嚟㔗垂鍙婪昇嚟启䫘潙䔇楗媕婘闼底埻垂䯄庖湺庖婺蓇垔䔇嘺橸昇嚟櫇毕䔇昄扞康係䂘麯庹幯滇婔湙䔇㔗啹溴螩崔䫘潙蔄荏凹劉庖媹傖媞閄嘪垄傸䩘溼䫌 username.tablename 䂇潊㔗套悩嘹婺懟婻䫘潙鄘录傺庖婔婻昇嚟認垂鍙婪儌滇 PostgreSQL 䔇臯婺㔗

劯湙婘 SQL 湺庖麯幘澇橬 public 昇嚟䔇楗媕㔗婺庖橔崓鍊庥婄镕冻湺庖嘹婉庫臖嘪䫘(埇脘䫔躿滇庫臖役鍴) public 昇嚟㔗

嘷䇽橬底昄扞康係䂘埇脘湹橸澇橬昇嚟潡蔙滇锔誺噕螩虘昄扞康螪閞準柊冕昇嚟䔇媘脘㔗套悩嘹驔襕婘認底係䂘婪幾昂闼幽婺庖橔崓鍊庥䔇䓂洉攓庫臖湹橸婉嘪䫘昇嚟㔗

劯锔饡釕嬉誕
溄鍊婪婔亓䂓欪