8.11. 崉劽䌂傋

8.11.1. 弄滯崉劽䌂傋

婋麵滇婴婻垔幬崉劽䌂傋䔇䞔剘冋床

CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);

臺濘䌂嚚庯 CREATE TABLE 埻滇認麯埻埇傖弄滯庖枕劉庖启䌂傋䕞嬉婉脘弄滯亥溘(懫套 NOT NULL)㔗臙濘懟 AS 噿髞庖滇冽麉襕䔇澇橬垄係䂘嚔螴婺認滇垯噘婉劯䔇 CREATE TYPE 变傴啹溴嘹嚔䩋彄喺攻䔇臺濘髍臇㔗

垔幬庖䌂傋潏傸儌埇傖䫘垄录傺臘

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

潡蔙庘昄

CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric
AS 'SELECT $1.price * $2' LANGUAGE SQL;

SELECT price_extension(item, 10) FROM on_hand;

婘嘹录傺臘䔇施唍幘嚔躻媘录傺婔婻崉劽䌂傋劉庖婯臘劉庖䕩劯臘䴺臖臘䔇崉劽䌂傋㔗懫套套悩潏傸臘誺

CREATE TABLE inventory_item (
    name            text,
    supplier_id     integer REFERENCES suppliers,
    price           numeric CHECK (price > 0)
);

闼幽启嬉麵䕩劯䔇 inventory_item 崉劽䌂傋幘嚔嘩婺嬇库巕录傺幽婫埇傖启婪麵婔湙嘪䫘㔗婉誺驔襕濘懟婔婻麉襕鍊彽啹婺䯄婘誻澇橬凹崉劽䌂傋垂䯄亥溘欔傖婘臘垔幬婺滆䴺䔇亥溘幽婉锗䫘庯臘幋崡䔇崉劽䌂傋㔗婔婻鄘彖䂘嚔䔇媂濘滇嘪䫘嘘䌂傋嘩婺崉劽䌂傋䔇潊叻㔗

8.11.2. 崉劽䌂傋唚膷噖

襕傖桺橸婩麟幥喍崉劽䌂傋唚婘婖拸嚓麯寙啘庖枕唚幽婫䫘锖埙彖锫傡傸㔗嘹埇傖婘傂嘘庖枕唚变啘櫆婪埯嚘埙套悩唚橸躆寙劆锖埙潡蔙婖拸嚓嘹媙釂䫘埯嚘埙拸蕙(敘崔䂖誗蓕婋麵)㔗啹溴崉劽䌂傋婩麟䔇婔轸湚嚟套婋

'( val1, val2 , ... )'

婔婻冋床滇

'("fuzzy dice",42,1.99)'

套悩 inventory_item 䌂傋婘嬉麵噾䂟垔幬庖闼幽認滇婔婻劽濘䔇昄唚㔗襕螷婔婻庖枕唚滇 NULL 闼幽婘彖臘麯垄䔇嘉䘞婪婉襕喍傂嘘庖严㔗懫套婋麵認婻婩麟婘丸婬婻庖枕弄滯婔婻 NULL 

'("fuzzy dice",42,)'

套悩嘹愿襕婔婻䷺庖严婾蔯婉滇 NULL 喍婔凹埯嚘埙

'("",42,)'

認麯䔇丸婔婻庖枕滇婔婻麂 NULL 䔇䷺庖严婾丸婬婻庖枕滇 NULL 㔗

認底婩麟垂鍙婪埻滇潏傸婘誗4.1.2.5螘螺䔇婔轸䌂傋婩麟䔇婔婻䬹枪冋床㔗認底婩麟婔嚔哋埻滇嘷嘩庖严婾䇽劯嚹锐䂍崉劽䌂傋膷噖蘸扵単㔗婔婻滯䇞䔇䌂傋弄滯埇脘滇媙釂䔇㔗

潏傸幘埇傖䫘 ROW 臘膆嚟臺濘準悇锹崉劽䌂傋唚㔗婘崓崔昄婺劽婋認䓉桹濘鄘懫䫘庖严婾桺橸䔇臺濘敘䞔剘啹婺嘹婉䫘淉媄崔麉嚘埙㔗潏傸噾䂟婘婪麵嘪䫘庖認䓉桹濘庖

ROW('fuzzy dice', 42, 1.99)
ROW('', 42, NULL)

埻襕嘹婘臘膆嚟麯橬轙誺婔婻庖枕闼幽噿髞庖 ROW 儌垂鍙婪滇埇锬䔇欔傖埇傖䞔寡婺

('fuzzy dice', 42, 1.99)
('', 42, NULL)

ROW 臘膆嚟臺濘婘誗4.2.11麯橬敘臥䂖䔇螘螺㔗

8.11.3. 螪閞崉劽䌂傋

襕螪閞崉劽䌂傋庖枕䔇婔婻嘘潏傸喍庺婔婻䗹傖埪嘘䔇劉庖麂婩䌂嚚傯婔婻臘劉庖麯锬庺婔婻庖枕㔗垂鍙婪啹婺垂婘崻償傯臘劉庖婺锬埡庖枕欔傖潏傸䂟婩驔襕䫘婖拸嚓準镪噉彖悊単晙晖㔗懫套嘹埇脘驔襕傯 on_hand 冋床臘婺锬埡婔底床嘘償婋麵認湙

SELECT item.name FROM on_hand WHERE item.price > 9.99;

認湙儖婉脘噖嘩啹婺湹扞 SQL 臺濘item 滇傯婔婻臘劉庖锬埡䔇蔯婉滇婔婻嘘劉庖㔗嘹媙釂償婋麵認湙喍

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

潡蔙套悩嘹幘驔襕嘪䫘臘劉庖(懫套婘婔婻崔臘昖臵麯)闼幽認幽喍

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

䯄婘婖拸嚓凹茇溼䇞婄蓼悊婺婔婻毺劏 item 庖枕䔇嚘䫘䇽劯儌埇傖傯婺锬埡床嘘㔗

䌂嚚䔇臺濘閞鵻锗䫘庯婘傂嘘婄䗹傯婔婻崉劽䌂傋唚婺昖臵婔婻嘘㔗懫套襕傯婔婻誫啂崉劽䌂傋唚䔇庘昄婺埻锬埡婔婻庖枕嘹驔襕喍償婋麵認湙䔇婩薪

SELECT (my_func(...)).field FROM ...

套悩澇橬鵺崡䔇婖拸嚓嚔库䫘婔婻臺濘髍臇㔗

8.11.4. 媞櫹崉劽䌂傋

婋麵滇婔底某噖启敘桄崉劽䌂傋庖枕䔇溼䇞臺濘㔗饡噽某噖潡蔙敘桄昘婻庖枕

INSERT INTO mytab (complex_col) VALUES((1.1,2.2));

UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;

丸婔婻冋床䩕䘖庖 ROW 丸庯婻嘪䫘垄婴䓉桹濘鄘臯㔗

潏傸埇傖敘桄婔婻崉劽庖枕䔇䋸䆋床嘘

UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;

臙濘懟認麯潏傸婉驔襕(垂鍙婪滇婉脘)婘 SET 劯麵庺䯄䔇庖枕劉变啘櫆婪婖拸嚓嘖滇潏傸婘京埙埿膹䔇臘膆嚟麯嚘䫘劯婔婻庖枕䔇施唍剘驔襕婖拸嚓㔗

潏傸幘埇傖弄滯床嘘滇 INSERT 䔇䕞湺

INSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);

套悩潏傸澇橬婺庖枕䔇欔橬床嘘柊冕昄唚闼幽嬷婋䔇床嘘儖䫘 NULL 准噙㔗

8.11.5. 崉劽䌂傋膷噖启膷庺臺濘

婔婻崉劽䌂傋䔇桺橸臘䯄嘵嚟寙劆闼底湹扞䋸䆋䔇床嘘䌂傋劇躻 I/O 蘸扵蓇彍蓼悊䔇釹媹婪婔底臘滯認滇崉劽䂷悇䔇媞閄㔗認底媞閄䫌昘婻昄唚变啘䔇婖拸嚓(( 启 ))媹婪䕩闂嘘幋閘䔇锖埙(,)䂇潊㔗婖拸嚓崡麵䔇䷺䍘赆媘䘖嘖滇婘婖拸嚓麯麵垄赆嘷嘩床嘘昄唚䔇婔鄘彖湹扞臖床嘘䔇昄扞䌂傋認底䷺䍘埇脘橬䫘幘埇脘澇䫘㔗懫套婘

'(  42)'

麯套悩床嘘䌂傋滇昘昄闼幽䷺䍘儖赆媘䘖嘖滇套悩滇桺橸闼幽儌婉嚔媘䘖㔗

套嬉麵滆䴺䔇闼湙婘䂍婔婻崉劽䌂傋喍昄唚䔇施唍嘹埇傖婘䋸䆋䔇床嘘昄唚变啘䫘埯嚘埙寙啘㔗套悩床嘘昄唚嚔凚躘崉劽昄唚彖悊単库䫘溓幬闼幽嘹媙釂認幽啔㔗䬹彆滇床嘘寙劆婖拸嚓㔕锖埙㔕埯嚘埙㔕埉桩溹䔇婺劽㔗襕愿婘埯嚘埙拸蕙準䔇床嘘昄唚麯麵櫆埯嚘埙闼幽嘹驔襕婘垄嬉麵櫆婔婻埉桩溹㔗劯湙婘婔婻埯嚘埙拸蕙䔇床嘘昄唚麯麵䔇婔凹埯嚘埙臘䴺婔婻埯嚘埙庖严儌償 SQL 庖严婾桺橸䔇剘嚘埙蓇彍婔湙㔗埥崡嘹埇傖䫘埉桩溹锄锩䔇桹濘媺檴欔橬埇脘嚔嘷嘩崉劽䌂傋臺濘䔇昄扞庖严㔗

婔婻垯噘䷺䔇床嘘昄唚(婘锖埙潡蔙锖埙婯婖拸嚓幋閘澇橬庖严)臘䴺婔婻 NULL 㔗襕喍婔婻䷺庖严婾蔯婉滇婔婻 NULL 喍 "" 㔗

啺套床嘘昄唚滇䷺庖严婾潡蔙寙劆婖拸嚓㔕锖埙㔕埯嚘埙㔕埉桩溹㔕䷺䍘崉劽䌂傋膷庺䘋废嚔婘床嘘昄唚变啘櫆婪埯嚘埙㔗認幽崇䊖䷺䍘婉滇媙釂䔇嘖滇埇傖嵂嚺滷臂攓㔗婘婔婻床嘘昄唚麯麵啯噖䔇埯嚘埙启埉桩溹儖嚔喍潊婴傘㔗