CREATE TYPE

NAME
SYNOPSIS
DESCRIPTION æè¿°
COMPOSITE TYPES å¤åç±»å
BASE TYPES åºæ¬ç±»å
ARRAY TYPES æ°ç»ç±»å
PARAMETERS åæ°
NOTES 注æ
EXAMPLES ä¾å
COMPATIBILITY å¼å®¹æ§
SEE ALSO åè§
è¯è
è·

---

NAME

CREATE TYPE - å®ä¹ä¸ä¸ªæ°çæ°æ®ç±»å

SYNOPSIS

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

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

DESCRIPTION æè¿°

CREATE TYPE 为å½åæ°æ®åºæ³¨åä¸ä¸ªæ°çæ°æ®ç±»åã å®ä¹è¯¥ç±»åçç¨æ·æ为å¶ææèã

å¦æç»åºæ¨¡å¼åï¼é£ä¹è¯¥ç±»åæ¯å¨æå®æ¨¡å¼ä¸å建ã å¦åå®æ¯å¨å½å模å¼ä¸- å建ãç±»ååå¿éååä¸æ¨¡å¼ä¸ä»»ä½ç°æçç±»åæèåä¸åã ï¼å ä¸ºè¡¨åæ°æ®ç±»åæèç³»ï¼ç±»ååä¸è½åå模å¼ä¸ç表ååå²çªãï¼

COMPOSITE TYPES å¤åç±»å

第ä¸ç§å½¢å¼ç CREATE TYPE å建ä¸ä¸ªå¤åç±»åã å¤åç±»åæ¯éè¿ä¸åå±æ§ååæ°æ®ç±»å声æçãè¿æ ·å®éä¸åä¸ä¸ªè¡¨çè¡ç±»åä¸æ ·ï¼ ä½æ¯å¦ææ们åªæ¯æ³å®ä¹ä¸ä¸ªç±»åï¼é£ä¹ä½¿ç¨ CREATE TYPE é¿åäºç´æ¥å建å®éç表ã ä¸ä¸ªç¬ç«çå¤åç±»å对äºä¸ä¸ªå½æ°çè¿åç±»åé常æç¨ã

BASE TYPES åºæ¬ç±»å

第äºç§å½¢å¼çCREATE TYPEå建ä¸ç§æ°çåºæ¬ç±»åï¼æ éç±»åï¼ã åæ°å¯ä»¥ä»¥ä»»æç顺åºåºç°ï¼èä¸æ¯ä¸é¢æ¾ç¤ºçé£æ ·ã并ä¸å¤§å¤æ°é½æ¯å¯éçã å®è¦æ±è¦å¨å®ä¹ç±»åä¹åå注å两个å½æ°ï¼ç¨CREATE FUNCTIONå½ä»¤ï¼ã æ¯æå½æ° input_function å output_function æ¯å¿é¡»çï¼ èå½æ° receive_function å send_function æ¯å¯éçã é常ï¼è¿äºå½æ°å¿é¡»ç¨ C æèå¶å®ä½å±è¯è¨ç¼åã

å½æ° input_function å°è¯¥ç±»åçå¤é¨ææ¬å½¢å¼è½¬æ¢æå¯ä»¥è¢«å¯¹è¯¥ç±»åæä½çæä½ç¬¦åå½æ°è¯å«çåé¨å½¢å¼ã output_function ç¨åç¸åç¨éã è¾å¥å½æ°å¯ä»¥å£°æ为æ¥åä¸ä¸ªç±»å为 c_string çåæ°ï¼æèæ¥åä¸ä¸ªç±»ååå«ä¸º c_stringï¼oidï¼integer çåæ°ã ï¼ç¬¬ä¸ä¸ªåæ°æ¯ C å- 串形å¼çè¾å¥ææ¬ï¼ç¬¬äºä¸ªæ¯å¨è¯¥ç±»å为æ°ç»ç±»åæ¶å¶åç´ çç±»åï¼ ç¬¬ä¸ä¸ªæ¯ç®æ å段çtypmodï¼å¦æå·²ç¥çè¯ãï¼ å®åºè¯¥è¿åä¸ä¸ªè¯¥æ°æ®ç±»åæ¬èº«çæ°å¼ã è¾åºå½æ°å¯ä»¥å£°æ为æ¥åä¸ä¸ªç±»å为æ°æ°æ®ç±»åçåæ°ï¼ æèæ¥å两个类åï¼ç¬¬äºä¸ªåæ°çç±»åæ¯ oidã 第äºä¸ªåæ°ä¹æ¯ç¨äºæ°ç»ç±»åçæ°ç»åç´ ç±»åãè¾åºå½æ°åºè¯¥è¿åç±»å cstringã

å¯éç receive_function æ该类åçå¤é¨äºè¿å¶è¡¨ç°å½¢å¼è½¬æ¢æåé¨è¡¨ç°å½¢å¼ã å¦æ没ææä¾è¿ä¸ªå½æ°ï¼é£ä¹è¯¥ç±»åä¸è½ç¨äºè¿å¶è¾å¥ãäºè¿å¶æ¼å¼åºè¯¥éåé£ç§è½¬æ¢æåé¨æ¼å¼æ¯è¾å®¹æçï¼åæ¶è¿æä¸å®ç§»æ¤æ§çã ï¼æ¯å¦ï¼æ åçæ´æ°æ°æ®ç±»å使ç¨ç½ç»å- èåºä½ä¸ºå¤é¨çäºè¿å¶è¡¨ç°å½¢å¼ï¼èåé¨è¡¨ç°å½¢å¼æ¯æºå¨çæ¬æºåèåºãï¼ æ¥æ¶å½æ°åºè¯¥å£°æ为æ¥åä¸ä¸ªç±»å为 internal çåæ°ï¼æè两个类ååå«ä¸º internal å oid çåæ°ã å®å¿é¡»è¿åä¸ä¸ªæ°æ®ç±»åèªèº«çæ°å¼ãï¼ç¬¬ä¸ä¸ªåæ°æ¯ä¸ä¸ªæåä¸ä¸ª StringInfo ç¼å²åºçï¼ä¿åæ¥ååè串çæéï¼ å¯éç第äºä¸ªåæ°æ¯åç´ ç±»åââå¦æç±»åæ¯ä¸ä¸ªæ°ç»ç±»åçè¯ãï¼ç±»ä¼¼çï¼å¯éç send_function æç±»å转æ¢ä¸ºå¤é¨äºè¿å¶è¡¨ç°å½¢å¼ã å¦æ没ææä¾è¿äºå½æ°ï¼é£ä¹ç±»åå°±ä¸è½ç¨äºè¿å¶æ¹å¼è¾åºãåéå½æ°å¯ä»¥å£°æ为æ¥æ¶ä¸ä¸ªæ°æ°æ®ç±»åï¼ æèæ¥æ¶ä¸¤ä¸ªåæ°ï¼ç¬¬äºä¸ªåæ°çç±»åæ¯ oidã第äºä¸ªåæ°ä»ç¶æ¯ç¨åæ°ç»ç±»åçã åéå½æ°å¿é¡»è¿å byteaã

è¿ä¸ªæ¶åä½åºè¯¥è§å¾å¥æªï¼å°±æ¯è¾å¥åè¾åºå½æ°æä¹å¯ä»¥å£°æ为è¿åæ°ç±»åçç»ææèæ¯æ¥åæ°ç±»åçåæ°ï¼ èä¸æ¯å¨æ°ç±»åå建ä¹åå°±éè¦å建å®ä»¬ã ç- æ¡æ¯è¾å¥å½æ°å¿é¡»åå建ï¼ç¶åæ¯è¾åºå½æ°ï¼æåæ¯æ°æ®ç±»åã PostgreSQL å°é¦åææ°æ°æ®ç±»åçååçä½è¾å¥å½æ°çè¿åç±»åã å®å°å建ä¸ä¸ª"壳"ç±»åï¼è¿ä¸ªç±»ååªæ¯å¨ pg_typeéé¢çä¸ä¸ªå ä½ç¬¦ï¼ç¶åæè¾å¥å½æ°å®ä¹åè¿ä¸ªå£³ç±»åè¿æ¥èµ·æ¥ã 类似çæ¯è¾åºå½æ°å°è¿æ¥å°ï¼ç°å¨å·²ç»åå¨ï¼ç壳类åãæåï¼ CREATE TYPE æè¿ä¸ªå£³ç±»åæ¿æ¢æå®æ´çç±»åå®ä¹ï¼è¿æ ·å°±å¯ä»¥ä½¿ç¨æ°ç±»åäºã

尽管æ°ç±»åçåé¨è¡¨ç°å½¢å¼åªæ I/O å½æ°åå¶å®ä½ å建æ¥ä½¿ç¨è¯¥ç±»åçå½æ°äºè§£ï¼ ä½åé¨è¡¨ç°è¿æ¯æå ä¸ªå±æ§å¿é¡»ä¸º PostgreSQL 声æã è¿äºä¸æéè¦çæ¯ internallengthã åºæ¬æ°æ®ç±»åå¯å®ä¹æ为å®é¿ï¼è¿æ¶ internallength æ¯ä¸ä¸ªæ£æ´æ°ï¼ä¹å¯ä»¥æ¯åé¿çï¼éè¿æ internallength 设置为 VARIABLE 表示ãï¼å¨åé¨ï¼è¿ä¸ªç¶æ æ¯éè¿å°typlen设置为 -1 å®ç°çãï¼ææåé¿ç±»åçåé¨å½¢å¼é½å¿é¡»ä»¥ä¸ä¸ªååèæ´æ°å¼å¤´ï¼è¿ä¸ªæ´æ°ç»åºæ- ¤ç±»åè¿ä¸ªæ°å¼çå¨é¿ã

å¯éçæ è®° PASSEDBYVALUE 表æ该类åçæ°å¼æ¯ç¨å¼ä¼ éçï¼ èä¸æ¯ç¨å¼ç¨ãä½ ä¸è½ä¼ éé£äºåé¨å½¢å¼å¤§äº Datum ï¼å¤§å¤æ°æºå¨ä¸æ¯ 4 å- èï¼æäºæ¯ 8 åèï¼ç±»åç尺寸çæ°æ®ç±»åçå¼ã

alignment åæ°å£°æ该æ°æ®ç±»åè¦æ±ç对é½åå¨æ¹å¼ã å许çæ°å¼çæäºæç§ 1ï¼2ï¼4ï¼æè 8 åèè¾¹ç对é½ã请注æåé¿ç±»åå¿é¡»æè³å° 4 åèç对é½ï¼ å ä¸ºå®ä»¬å¿é¡»åå«ä¸ä¸ª int4 ä½ä¸ºå®ä»¬ç第ä¸ä¸ªæ份ã

storage åæ°å许为åé¿æ°æ®ç±»åéæ©åå¨çç¥ã ï¼å®é¿ç±»ååªåè®¸ä½¿ç¨ plainï¼ã plain 声æ该æ°æ®ç±»åæ»æ¯ç¨åèçæ¹å¼èä¸æ¯å缩çæ¹å¼åå¨ã extended 声æç³»ç»å°é¦åè¯å¾å缩ä¸ä¸ªé¿çæ°æ®å¼ï¼ç¶åå¦æå®ä»ç¶å¤ªé¿çè¯å°±å°å®çå¼ç§»åºä¸»è¡¨çè¡ï¼ ä½ç³»ç»å°ä¸ä¼å缩å®ã main å许å缩ï¼ä½æ¯ä¸èµæææ°å¼ç§»å¨åºä¸»è¡¨ã ï¼ç¨è¿ç§åå¨çç¥çæ°æ®é¡¹å¯è½ä»å°ç§»å¨åºä¸»è¡¨ï¼å¦æä¸è½æ¾å¨ä¸è¡éçè¯ï¼ ä½æ¯å®ä»¬å°æ¯ extended å external 项æ´æ¿æåå¨ä¸»è¡¨éãï¼

å¦æç¨æ·å¸æå段çæ°æ®ç±»å缺çæ¶ä¸æ¯ NULLï¼èæ¯å¶å®ä»ä¹ä¸è¥¿ï¼ é£ä¹ä½ å¯ä»¥å£°æä¸ä¸ªç¼ºçå¼ã å¨ DEFAULT å³é®åéé¢å£°æ缺çå¼ã ï¼è¿æ ·ç缺çå¯ä»¥è¢«éçå¨ç¹å®å段ä¸çæç¡®ç DEFAULT åå¥è¦çãï¼

è¦è¡¨ç¤ºä¸ä¸ªç±»åæ¯æ°ç»ï¼ç¨ ELEMENT å³é®å声ææ°ç»åç´ çç±»åã æ¯å¦ï¼è¦å®ä¹ä¸ä¸ª 4 åèæ´æ°(int4)çæ°ç»ï¼å£°æ

ELEMENT = int4

ã æå³æ°ç»ç±»åçæ´å¤ç»èå¨ä¸é¢æè¿°ã

è¦å£°æç¨äºè¿ç§ç±»åæ°ç»çå¤é¨å½¢å¼çæ°å¼ä¹é´çåé符ï¼å¯ç¨ delimiter 声ææå®åé符ã缺ççåé符æ¯éå·ï¼,ï¼ã 请注æåé符æ¯åæ°ç»åç´ ç±»åç¸å³èï¼èä¸æ¯æ°ç»ç±»åæ¬èº«ã

ARRAY TYPES æ°ç»ç±»å

å¨å建ç¨æ·å®ä¹æ°æ®ç±»åçæ¶åï¼PostgreSQL èªå¨å建ä¸ä¸ªä¸ä¹å³èçæ°ç»ç±»åï¼å¶ååç±è¯¥åºæ¬ç±»åçåååç¼ä¸ä¸ªä¸å线ç»æã åæå¨ç解è¿ä¸ªå½åä¼ ç»ï¼å¹¶ä¸æ对类å为 foo[] çå- 段ç请æ±è½¬æ¢æ对类å为 _foo çå- 段ç请æ±ãè¿ä¸ªéå«å建çæ°ç»ç±»åæ¯åé¿å¹¶ä¸ä½¿ç¨å建çè¾å¥åè¾åºå½æ° array_in å array_outã

ä½ å¾å¯è½ä¼é®å¦æç³»ç»èªå¨å¶ä½æ£ç¡®çæ°ç»ç±»åï¼é£ä¸ºä»ä¹æ个 ELEMENTé项ï¼ä½¿ç¨ ELEMENT æç¨çå¯ä¸çåºåæ¯å¨ä½å¶ä½çå®é¿ç±»å碰巧å¨åé¨æ¯ä¸ä¸ªä¸å®æ°ç®ç¸åäºç©çæ°ç»ï¼ èä½ åæ³åè®¸è¿ N 个äºç©å¯ä»¥éè¿èæ ç´æ¥å³èï¼ä»¥åé£äºä½ åå¤æ该类åå½åæ´ä½è¿è¡çæä½ã æ¯å¦ï¼ç±»å name å°±å许å¶ææ char ç¨è¿ç§æ¹æ³å³èã ä¸ä¸ªäºç»´ç point ç±»åä¹å¯ä»¥å许å¶ä¸¤ä¸ªæææµ®ç¹åæç§ç±»ä¼¼ point[0] å point[1] çæ¹æ³å³èã 请注æè¿ä¸ªåè½åªéç¨ä¸é£äºåé¨å½¢å¼æ¯ä¸ä¸ªç¸åçå®é¿åçåºåçç±»åã ä¸ä¸ªå¯ä»¥èæ åçåé¿ç±»åå¿é¡»æ被 array_in å array_out 使ç¨çä¸è¬åçåé¨è¡¨ç°å½¢å¼ã åºäºåå²ååï¼ä¹å°±æ¯è¯´ï¼é£äºææ¾é误ä½è¡¥ææ¥å¾å¤ªè¿çé®é¢ï¼ï¼å®é¿æ°ç»ç±»åçèæä»é¶å¼å§ï¼èä¸æ¯è±¡åé¿ç±»åé£æ·çä»ä¸å¼å§ã

PARAMETERS åæ°

name

å°è¦å建çç±»ååï¼å¯ä»¥æ模å¼ä¿®é¥°ï¼ã

attribute_name

å¤åç±»åçä¸ä¸ªå±æ§ï¼å段ï¼çååã

data_type

ä¸ä¸ªè¦æ为ä¸ä¸ªå¤åç±»åçå段çç°ææ°æ®ç±»åçååã

input_function

ä¸ä¸ªå½æ°çåç§°ï¼ å°æ°æ®ä»å¤é¨ç±»å转æ¢æåé¨ç±»åã

output_function

ä¸ä¸ªå½æ°çåç§°ï¼ å°æ°æ®ä»åé¨æ ¼å¼è½¬æ¢æéäºæ¾ç¤ºçå½¢å¼ã

receive_function

ææ°æ®ä»ç±»åçå¤é¨äºè¿å¶å½¢å¼è½¬æ¢æå¶åé¨å½¢å¼çå½æ°çååã

send_function

ææ°æ®ä»ç±»åçåé¨å½¢å¼è½¬æ¢æå¶å¤é¨äºè¿å¶å½¢å¼çå½æ°åã

internallength

ä¸ä¸ªæ°å¼å¸¸éï¼è¯´ææ°ç±»åçåé¨è¡¨ç°å½¢å¼çé¿åº¦ã缺ççå设æ¯å®æ¯åé¿çã

alignment

该æ°æ®ç±»åçåå¨å¯¹é½è¦æ±ãå¦æ声æäºï¼å¿é¡»æ¯ charï¼ int2ï¼ int4 æ doubleï¼ ç¼ºçæ¯ int4ã

storage

该æ°æ®ç±»åçåå¨çç¥ãå¦æ声æäºï¼å¿é¡»æ¯ plainï¼externalï¼ extendedï¼æ mainï¼ ç¼ºçæ¯ plainã

default

该类åç缺çå¼ãé常æ¯çç¥å®çï¼æ以缺çæ¯ NULLã

element

被å建çç±»åæ¯æ°ç»ï¼è¿ä¸ªå£°ææ°ç»åç´ çç±»åã

delimiter

å°ç¨åæ°ç»çæ°æ®åç´ ä¹é´åé符çå符ã

NOTES 注æ

ç¨æ·å®ä¹ç±»ååä¸è½ä»¥ä¸å线ï¼_ï¼ å¼å¤´èä¸åªè½æ 62 个å符é¿ãï¼æèéå¸¸æ¯ NAMEDATALEN-2ï¼ èä¸æ¯å¶å®ååé£æ ·çå¯ä»¥æ NAMEDATALEN-1 个å符ï¼ã 以ä¸å线å¼å¤´çç±»åå被解ææåé¨å建çæ°ç»ç±»ååã

å¨ PostgreSQL çæ¬ 7.3 以åï¼æ们è¦éè¿ä½¿ç¨å ä½ä¼ªç±»å opaque 代æ¿å½æ°çååå¼ç¨æ¥é¿åå建壳类åã 7.3 ä¹å cstring åæ°åç»æåæ ·éè¦å£°æ伪 opaqueã è¦æ¯æè£è½½æ§ç转å¨å¤é£é´ï¼CREATE TYPE å°æ¥åé£äºç¨ opaque声æçå½æ°ï¼ ä½æ¯å®åååºä¸æ¡éç¥å¹¶ä¸ç¨æ£ç¡®çç±»åæ¹åå½æ°ç声æã

EXAMPLES ä¾å

è¿ä¸ªä¾åå建ä¸ä¸ªå¤åç±»å并ä¸å¨ä¸ä¸ªå½æ°å®ä¹ä¸ä½¿ç¨å®ï¼

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 (
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
);

æ´å¤çä¾åï¼åæ¬åéçè¾å¥åè¾åºå½æ°ï¼å¨ Chapter 31‘‘Extending SQL’’ in the documentationã

COMPATIBILITY å¼å®¹æ§

CREATE TYPE å½ä»¤æ¯ PostgreSQL æ©å±ãå¨ SQL99 éæä¸ä¸ª CREATE TYPE è¯- å¥ï¼ä½æ¯ç»èä¸å PostgreSQL çææ¯è¾å¤§åºå«ã

SEE ALSO åè§

CREATE FUNCTION [create_function(7)], DROP TYPE [drop_type(l)]

è¯è

Postgresql ä¸æç½ç« ä½ä¼å¹³ <laser@pgsqldb.org>

è·

æ¬é¡µé¢ä¸æçç±ä¸æ man æå页计åæä¾ã
ä¸æ man æå页计åï¼https://github.com/man-pages-zh/manpages-zh

---