PostgreSQL 8.2.3 婺桺桺懼
劯锔媆锔媆誕嬉誕

CREATE TYPE

劉䓄

CREATE TYPE -- 垔幬婔婻桄昄扞䌂傋

臺濘

CREATE TYPE name AS
    ( attribute_name data_type [, ... ] )

CREATE TYPE name (
    INPUT = input_function,
    OUTPUT = output_function
    [ , RECEIVE = receive_function ]
    [ , SEND = send_function ]
    [ , ANALYZE = analyze_function ]
    [ , INTERNALLENGTH = { internallength | VARIABLE } ]
    [ , PASSEDBYVALUE ]
    [ , ALIGNMENT = alignment ]
    [ , STORAGE = storage ]
    [ , DEFAULT = default ]
    [ , ELEMENT = element ]
    [ , DELIMITER = delimiter ]
)

CREATE TYPE name

柟誄

CREATE TYPE 婺嘷嬉昄扞康濘喯婔婻桄䔇昄扞䌂傋㔗垔幬臖䌂傋䔇䫘潙潊婺噽欔橬蔙㔗

套悩䂍庺昇嚟劉闼幽臖䌂傋滇婘毺垔昇嚟婺录傺㔗劥彍垄儖婘嘷嬉昇嚟婺录傺㔗䌂傋劉媙驔启劯婔昇嚟婺傂嘘䯄橬䔇䌂傋潡蔙嘘婉劯㔗啹婺臘启昄扞䌂傋橬蕫係欔傖䌂傋劉幘婉脘启劯昇嚟婺䔇臘劉庖喾仕㔗

崉劽䌂傋

丸婔䓉嘵嚟䔇 CREATE TYPE 录傺婔婻崉劽䌂傋㔗崉劽䌂傋滇锔誺婔彖匂攓劉启昄扞䌂傋弄滯䔇㔗認湙垂鍙婪启婔婻臘䔇臯䌂傋婔湙嘖滇套悩埻滇愿垔幬婔婻䌂傋闼幽嘪䫘 CREATE TYPE 儌埇傖镪噉䕘毖录傺垂鍙䔇臘㔗婔婻䋸䆋䔇崉劽䌂傋啔婔婻庘昄䔇埗昄潡蔙誫啂䌂傋滇麂婩橬䫘䔇㔗

嘺橸䌂傋

丸庯䓉嘵嚟䔇CREATE TYPE 录傺婔䓉桄䔇嘺橸䌂傋(湺麟䌂傋)㔗埗昄埇傖毬傂懟釺废庺䯄蔯婉滇婪麵滆䴺䔇闼湙幽婫崓崔昄鄘滇埇锬䔇㔗垄襕挗襕婘垔幬䌂傋幋嬉噽䫘 CREATE FUNCTION 濘喯婴婻潡敘崔婻庘昄㔗櫇毕庘昄 input_function 启 output_function 滇媙釂䔇蔯庘昄 receive_function, send_function, analyze_function 滇埇锬䔇㔗锔婩認底庘昄媙釂䫘 C 潡蔙噽垄嘯北臺蘔䚡喍㔗

input_function 庘昄儖臖䌂傋䔇崡鄘桺橸嘵嚟蘸扵潊埇傖赆臖䌂傋䔇淉嘩严启庘昄臖彆䔇喙鄘嘵嚟㔗output_function 䫘锫䕩埉㔗膷噖庘昄埇傖弄滯婺毖埖婔婻䌂傋婺 cstring 䔇埗昄潡蔙毖埖婬婻䌂傋彖彆婺 cstring, oid, integer 䔇埗昄㔗丸婔婻埗昄滇 C 庖严婾嘵嚟䔇膷噖桺橸丸庯婻埗昄滇臖䌂傋躻躆䔇 OID(昄䂇䌂傋鍴崡認䓉愙喕婋垄傸毖埖躻躆噄䘹䔇䌂傋 OID)丸婬婻滇䕞湺庖枕䔇 typmod(套悩橻䘖彍嚹锐 -1)㔗膷噖庘昄媙釂誫啂婔婻躻躆昄扞䌂傋䔇唚㔗锔婩膷噖庘昄庫嘷赆弄滯婺 STRICT 劥彍嘷臂埡 NULL 膷噖施儖赆嘪䫘丸婔婻埗昄婺 NULL 誕臯脄䫘幽婫媙釂傉䇽誫啂 NULL 潡檖髍㔗認婻䬹攓婂襕滇婺庖櫇毕嘘膷噖庘昄認䓉庘昄埇脘驔襕拐䂺 NULL 膷噖㔗膷庺庘昄媙釂赆弄滯婺毖埖婔婻桄昄扞䌂傋䔇埗昄幽婫媙釂誫啂 cstring 䌂傋㔗膷庺庘昄婉嚔赆嘪䫘 NULL 脄䫘㔗

埇锬䔇 receive_function 檪臖䌂傋䔇崡鄘庯誕彽臘䯄嘵嚟蘸扵潊喙鄘臘䯄嘵嚟㔗套悩澇橬柊冕認婻庘昄闼幽臖䌂傋婉脘䫘庯誕彽膷噖㔗庯誕彽湚嚟庫臖锬埡闼䓉懫膄垹滷蘸扵劯施誻橬婔垔䓂洉攓䔇喙鄘湚嚟㔗懫套湺庖䔇昘昄昄扞䌂傋嘪䫘䘏䂩庖誗废嘩婺崡鄘䔇庯誕彽臘䯄嘵嚟蔯喙鄘臘䯄嘵嚟彍滇橺単䔇橸橺庖誗废㔗冋套毖櫽庘昄庫臖弄滯婺毖埖婔婻䌂傋婺 internal 䔇埗昄潡蔙滇婬婻䌂傋彖彆婺 internal, oid, integer 䔇埗昄㔗丸婔婻埗昄滇婔婻毺劏婔婻 StringInfo 䚷喾寺䔇㔕媺庻毖埖庖誗婾䔇毺鐽埇锬䔇埗昄启桺橸膷噖庘昄婔湙㔗毖櫽庘昄媙釂誫啂婔婻臖䌂傋䔇昄扞唚㔗锔婩毖櫽庘昄庫嘷赆弄滯婺 STRICT 劥彍劥彍嘷臂埡 NULL 膷噖施儖赆嘪䫘丸婔婻埗昄婺 NULL 誕臯脄䫘幽婫媙釂傉䇽誫啂 NULL 潡檖髍㔗認婻䬹攓婂襕滇婺庖櫇毕嘘毖櫽庘昄認䓉庘昄埇脘驔襕拐䂺 NULL 膷噖㔗劯湙埇锬䔇 send_function 檪䌂傋䔇喙鄘臘䯄嘵嚟蘸扵婺崡鄘庯誕彽臘䯄嘵嚟㔗套悩澇橬柊冕認底庘昄闼幽䌂傋儌婉脘䫘庯誕彽桹嚟膷庺㔗埏锕庘昄媙釂弄滯婺毖櫽婔婻桄昄扞䌂傋幽婫媙釂誫啂 bytea 䂷悩㔗埏锕庘昄婉嚔赆傖 NULL 唚脄䫘㔗

認婻施唍嘹庫臖蓬冖喺攻膷噖启膷庺庘昄支幽埇傖弄滯婺誫啂桄䌂傋䔇䂷悩潡蔙滇毖埖桄䌂傋䔇埗昄蔯婫滇婘桄䌂傋录傺幋嬉儌驔襕录傺垄傸㔗享懽滇䌂傋媙釂赆饡噽垔幬婺婔婻弿䌂傋垄埻滇婔婻鍴庖劉䓄启匂婂幋崡澇橬噽傡匂攓䔇剹嘉严䌂傋㔗認埇傖锔誺澇橬噽傡鵺崡埗昄䔇 CREATE TYPE name 变傴準垯潊㔗䇽劯儌埇傖嚘䫘臖弿䌂傋垔幬膷噖膷庺庘昄㔗橔劯CREATE TYPE 檪認婻弿䌂傋敪扵潊垯昘䔇㔕橬昽䔇䌂傋垔幬認湙儌埇傖嘪䫘桄䌂傋庖㔗

埇锬䔇 analyze_function 婺臖昄扞䌂傋䔇庖枕欓臯婯臖䌂傋䕩噿䔇䂘螇媇敇櫽镖㔗䚺䩕䔇施套悩臖䌂傋橬婻䚺䩕䔇 B-tree 淉嘩严䌂闼幽 ANALYZE 儖儺臘嘪䫘臖䌂傋䔇"京庯"启"償庯"淉嘩严櫽镖媇敇㔗凹庯麂湺麟䌂傋認䓉臯婺冽埇脘婉劽锗啹溴埇傖锔誺柊冕婔婻躻垔幬䔇彖悊庘昄襖䕡垄㔗彖悊庘昄媙釂弄滯婺毖櫽剘䋸婔婻 internal 䌂傋䔇埗昄幽婫誫啂婔婻 boolean 䂷悩㔗彖悊庘昄䔇臥䂖 API 婘 src/include/commands/vacuum.h 麯㔗

儘䞇桄䌂傋䔇喙鄘臘䯄嘵嚟埻橬膷噖膷庺庘昄启噽垄嘹录傺準嘪䫘臖䌂傋䔇庘昄庖蓼嘖喙鄘臘䯄嘵嚟誻滇橬庹婻匂攓媙釂婺 PostgreSQL 弄滯㔗internallength 滇橔麉襕䔇婔婻㔗嘺橸昄扞䌂傋埇垔幬潊婺垔阪認施 internallength 滇婔婻溼昘昄幘埇傖滇埻阪䔇認施䫘檪 internallength 螆婺 VARIABLE 臘䴺(婘喙鄘認婻䪽攕滇锔誺儖 typlen 螆䘞婺-1 垂䯄䔇)㔗欔橬埻阪䌂傋䔇喙鄘嘵嚟鄘媙釂傖婔婻啕庖誗昘昄嚔崘認婻昘昄䂍庺溴䌂傋認婻昄唚䔇噘阪㔗

埇锬䔇湺螄 PASSEDBYVALUE 臘滯臖䌂傋䔇昄唚滇毬唚蔯婉滇嚘䫘嚹锐㔗嘹婉脘嚹锐闼底喙鄘嘵嚟崓庯 Datum 䌂傋儺凩(崓崔昄橺単婪滇 4 庖誗橬底滇 8 庖誗)䔇昄扞䌂傋䔇唚㔗

alignment 埗昄弄滯臖昄扞䌂傋襕挗䔇凹阊庻嗘桹嚟㔗噕螩䔇昄唚京昽庯毬䙓 1, 2, 4, 8 庖誗膹䘯凹阊㔗臙濘懟埻阪䌂傋媙釂橬躿儏 4 庖誗䔇凹阊啹婺垄傸媙釂寙劆婔婻 int4 嘩婺丸婔婻鄘彖㔗

storage 埗昄噕螩婺埻阪昄扞䌂傋锬拷庻嗘亡䘖(垔阪䌂傋埻噕螩嘪䫘 plain)㔗plain 弄滯臖昄扞䌂傋攂滇䫘喙蕫䔇桹嚟蔯婉滇寋䚷䔇桹嚟庻嗘㔗extended 弄滯係䂘儖饡噽臘商寋䚷婔婻阪䔇昄扞唚䇽劯套悩垄傉䇽崻阪䔇臺儌儖垄䔇唚䓂庺婂臘嘖係䂘儖婉嚔寋䚷垄㔗external 弄滯䥕溵係䂘誕臯寋䚷幽婫噕螩儖垄䔇唚䓂庺婂臘㔗main 噕螩寋䚷嘖滇婉蕂潊檪昄唚䓂媘庺婂臘(套悩垂婘婉脘櫆婘婔臯麯䔇臺傉儖䓂媘庺婂臘)垄懫 extended 启 external 釹敘懪懟媺庻婘婂臘麯㔗

套悩䫘潙婯橕庖枕䔇昄扞䌂傋䚺䩕施婉滇 NULL 闼幽埇傖婘 DEFAULT 噿髞庖麯弄滯婔婻䚺䩕唚(埇傖赆鍇五婘䬹垔庖枕婪䔇 DEFAULT 床埖襖䕡)㔗

䫘 ELEMENT 噿髞庖弄滯昄䂇噄䘹䔇䌂傋㔗懫套ELEMENT = int4 垔幬庖婔婻 4 庖誗昘昄(int4)䔇昄䂇㔗橬噿昄䂇䌂傋䔇敘崔䂖誗婘婋麵柟誄㔗

埇䫘 delimiter 毺垔䫘庯認䓉䌂傋昄䂇䔇崡鄘嘵嚟䔇昄唚幋閘䔇彖锫严㔗䚺䩕䔇彖锫严滇锖埙(,)㔗臙濘懟彖锫严滇启昄䂇噄䘹䌂傋䕩噿蕫蔯婉滇昄䂇䌂傋橸躆㔗

昄䂇䌂傋

婘录傺䫘潙垔幬昄扞䌂傋䔇施唍PostgreSQL 躻媘录傺婔婻婯幋噿蕫䔇昄䂇䌂傋噽劉庖䫌臖嘺橸䌂傋䔇劉庖嬉䚔婔婻婋彐亪䂇潊㔗彖悊単䊖蓼認婻变劉嚹䂘幽婫檪凹䌂傋婺 foo[] 䔇庖枕䔇臙挗蘸扵潊凹䌂傋婺 _foo 䔇庖枕䔇臙挗㔗認婻锊劆录傺䔇昄䂇䌂傋滇埻阪幽婫嘪䫘喙傺䔇 array_in 启 array_out 膷噖启膷庺庘昄㔗

嘹冽埇脘嚔閞套悩係䂘躻媘彽嘩溼䇞䔇昄䂇䌂傋闼婺傔幽誻襕橬婻 ELEMENT 锬釹?嘪䫘 ELEMENT 䔇婺劽橬庯嘹垔幬䔇垔阪䌂傋䵄噓婘喙鄘滇婔婻婔垔昄䕞䕩劯庋䬷䔇昄䂇蔯嘹埽愿噕螩認 N 婻庋䬷埇傖锔誺婋湺䕘毖噿蕫昊底淉嘩严儖檪臖䌂傋嘷啔昘嘷誕臯崇䊖㔗 懫套悇潊 name 䌂傋䔇 char 潊叻儌噕螩䫘認䓉桹濘噿蕫㔗婔婻庯䂘䔇 point 䌂傋幘埇傖噕螩噽婴婻敞䗹傋悇潊潊叻毬䙓䌂嚚 point[0] 启 point[1] 䔇桹濘噿蕫㔗臙濘懟認婻媘脘埻锗䫘庯垔阪䌂傋幽婫噽喙鄘嘵嚟滇婔婻䕩劯垔阪嘘䔇废彖㔗婔婻埇傖婋湺寡䔇埻阪䌂傋媙釂橬赆 array_in 启 array_out 嘪䫘䔇婔轸寡䔇喙鄘臘䯄嘵嚟㔗庺庯寖埾寘啹垔阪昄䂇䌂傋䔇婋湺傯 0 嚔哋蔯婉滇償埻阪䌂傋闼湙䔇傯 1 嚔哋㔗

埗昄

name

儖襕录傺䔇䌂傋劉(埇傖橬昇嚟媞閄)

attribute_name

崉劽䌂傋䔇婔婻匂攓(庖枕)䔇劉庖

data_type

襕潊婺婔婻崉劽䌂傋䔇庖枕䔇䯄橬昄扞䌂傋䔇劉庖

input_function

婔婻庘昄䔇劉䓄儖昄扞傯崡鄘桺橸嘵嚟蘸扵潊喙鄘湚嚟㔗

output_function

婔婻庘昄䔇劉䓄儖昄扞傯喙鄘湚嚟蘸扵潊锗庯滆䴺䔇崡鄘桺橸嘵嚟㔗

receive_function

檪昄扞傯䌂傋䔇崡鄘庯誕彽嘵嚟蘸扵潊喙鄘嘵嚟䔇庘昄劉䓄

send_function

檪昄扞傯䌂傋䔇喙鄘嘵嚟蘸扵潊崡鄘庯誕彽嘵嚟䔇庘昄劉䓄

analyze_function

婺臖昄扞䌂傋欓臯䂘螇彖悊䔇庘昄劉

internallength

婔婻昄唚婩麟臘滯桄䌂傋䔇喙鄘臘䯄嘵嚟䔇阪庥㔗䚺䩕啺垔垄滇埻阪䔇㔗

alignment

臖昄扞䌂傋䔇庻嗘凹阊襕挗㔗套悩弄滯庖媙釂滇 char, int2, int4(䚺䩕), double 幋婔㔗

storage

臖昄扞䌂傋䔇庻嗘亡䘖㔗套悩弄滯庖媙釂滇 plain(䚺䩕), external, extended, main 幋婔㔗

default

臖䌂傋䔇䚺䩕唚㔗苖䩕䘖彍婺 NULL

element

赆录傺䔇䌂傋滇昄䂇認婻弄滯昄䂇噄䘹䔇䌂傋㔗

delimiter

昄䂇噄䘹幋閘彖锫严

濘懟

䫘潙垔幬䌂傋劉婉脘傖婋彐亪(_)嚔崘蔯婫埻脘橬 62 婻庖严阪潡蔙臘滇 NAMEDATALEN-2 婻(蔯婉滇噽垄劉庖闼湙䔇埇傖橬 NAMEDATALEN-1 婻庖严)㔗傖婋彐亪嚔崘䔇䌂傋劉赆蓼悊潊喙鄘录傺䔇昄䂇䌂傋劉㔗

啹婺婔斥䌂傋赆录傺幋劯凹垄䔇嘪䫘儌澇橬鍊彽欔傖录傺婔婻嘺橸䌂傋儌京備庯毽庽欔橬䫘潙欓臯䌂傋垔幬婺毺垔䔇劇婻庘昄䔇溄鍊劯施臖䌂傋䔇录傺蔙幘儖滇認底庘昄䔇拖橬蔙㔗認凹庯崓崔昄䌂傋垔幬婺毺垔䔇庘昄準臘婉嚔锹潊傔幽婉謇閞鵻㔗嘖滇套悩嘹螆螇䔇桄䌂傋婘喙鄘嘵嚟启崡鄘嘵嚟幋閘蘸扵䔇施唍嘪䫘"星懘媇敇"闼幽嘹傉䇽襕喉婬蔄荏㔕崔媹償媄㔗

婘 PostgreSQL 8.2 幋嬉䔇䬽橸婺幽婉庻婘 CREATE TYPE name 臺濘㔗录傺桄䔇嘺橸䌂傋幋嬉媙釂饡噽录傺噽膷噖庘昄㔗認湙PostgreSQL 儖嚔饡噽檪桄䌂傋䔇劉庖䩋嘩膷噖庘昄䔇誫啂䌂傋幽锊劆录傺弿䌂傋䇽劯認婻弿䌂傋儖赆锟劯垔幬䔇膷噖膷庺庘昄嚘䫘㔗認䓉蔕嚟䔇桹濘䕞嬉傉䇽赆櫇毕嘖噾䂟埉凹嘪䫘儖準埇脘婉喉櫇毕㔗劯湙婺庖镪噉庘昄垔幬婺䔇婘施弿䌂傋啽䇽婄抂幌係䂘臘嘷膷噖庘昄䫘 C 臺蘔幥喍施儖埻脘䫘認䓉桹濘录傺弿䌂傋㔗

婘 PostgreSQL 7.3 傖嬉襕锔誺嘪䫘剹嘉嚻䌂傋 opaque 傼敪庘昄䔇嬉劏嚘䫘準镪噉录傺弿䌂傋㔗7.3 幋嬉 cstring 埗昄启䂷悩劯湙驔襕弄滯嚻 opaque 㔗襕櫇毕媹蘘斓䔇蘸嗘桺傽CREATE TYPE 儖毖埖闼底䫘 opaque 弄滯䔇膷噖膷庺庘昄嘖滇垄儖埏庺婔溇锔䘖幽婫䫘溼䇞䔇䌂傋櫹埻庘昄䔇弄滯㔗

冋床

認婻冋床录傺婔婻崉劽䌂傋幽婫婘婔婻庘昄垔幬婺嘪䫘垄

CREATE TYPE compfoo AS (f1 int, f2 text);

CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
    SELECT fooid, fooname FROM foo
$$ LANGUAGE SQL;

認婻变傴录傺 box 昄扞䌂傋幽婫儖認䓉䌂傋䫘庯婔婻臘垔幬

CREATE TYPE box;

CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ;
CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS ... ;

CREATE TYPE box (
    INTERNALLENGTH = 16,
    INPUT = my_box_in_function,
    OUTPUT = my_box_out_function
);

CREATE TABLE myboxes (
    id integer,
    description box
);

套悩 box 䔇喙鄘䂷悇滇婔婻啕婻 float4 䔇昄䂇埇傖嘪䫘

CREATE TYPE box (
    INTERNALLENGTH = 16,
    INPUT = my_box_in_function,
    OUTPUT = my_box_out_function,
    ELEMENT = float4
);

準噕螩婔婻 box 䔇昄唚潊彖潊叻埇傖䫘婋湺螪閞㔗劥彍臖䌂傋启嬉麵䔇臯婺婔湙㔗

認溇变傴录傺婔婻崓凹茇䌂傋幽儖噽䫘庯婔婻臘垔幬

CREATE TYPE bigobj (
    INPUT = lo_filein, OUTPUT = lo_fileout,
    INTERNALLENGTH = VARIABLE
);
CREATE TABLE big_objs (
    id integer,
    obj bigobj
);

敘崔䔇冋床寙拸劽锗䔇膷噖启膷庺庘昄嘉庯誗33.11㔗

噚垹攓

CREATE TYPE 变傴滇 PostgreSQL 欷匘㔗婘 SQL 麯橬婔婻 CREATE TYPE 臺埖嘖滇䂖誗启 PostgreSQL 橬懫膄崓寺彆㔗

埽蓕

CREATE FUNCTION, DROP TYPE, ALTER TYPE, CREATE DOMAIN
劯锔饡釕嬉誕
CREATE TRIGGER婪婔亓CREATE USER