37.4. 弄滯

user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
arow RECORD;

name [CONSTANT] type [NOT NULL] [{ DEFAULT | := } expression];

quantity integer DEFAULT 32;
url varchar := 'http://mysite.com';
user_id CONSTANT integer := 10;

37.4.1. 庘昄埗昄䔇彆劉

嚹锐䂍庘昄䔇埗昄鄘滇䫘 $1, $2 京京認湙䔇湺臖严㔗婺庖嵂媹埇臂攓埇傖婺 $n 埗昄劉弄滯彆劉㔗䇽劯彆劉潡蔙昄庖湺臖严鄘埇傖毺劏埗昄唚㔗

橬婴䓉录傺彆劉䔇桹濘懫膄喘䔇滇婘 CREATE FUNCTION 变傴麯䂍庺埗昄劉懫套

CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

埥崡婔婻桹濘滇 PostgreSQL 8.0 傖嬉䔇嫇婔䔇桹濘滇滯䇞婄弄滯婺彆劉嘪䫘弄滯臺濘

name ALIAS FOR $n;

認婻鼯湚䔇劯婔婻冋床䩋蕙準償婋麵認湙

CREATE FUNCTION sales_tax(real) RETURNS real AS $$
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

敘崔冋床

CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
DECLARE
    v_string ALIAS FOR $1;
    index ALIAS FOR $2;
BEGIN
    -- 認麯櫆婔底嘪䫘 v_string 启 index 䔇螇䞖
END;
$$ LANGUAGE plpgsql;


CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
BEGIN
    RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
END;
$$ LANGUAGE plpgsql;

套悩婔婻 PL/pgSQL 庘昄弄滯婺劆橬膷庺埗昄闼幽儌嚔䂍庽膷庺埗昄 $n 䔇劉庖傖埪埇锬䔇彆劉桹濘启噽垄溼婩膷噖埗昄婔湙㔗婔婻膷庺埗昄垂鍙婪滇彺哋唚婺 NULL 䔇埻麟婘庘昄欓臯䔇誺䘋婺庫臖䂍垄蕋唚㔗臖埗昄䔇橔劯昄唚滇誫啂䔇婩薪㔗懫套體嫞鵺-䘯蘹䔇冋床幘埇傖認幽啔

CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
    tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

臙濘懟媘䘖庖 RETURNS real 嘷䇽幘埇傖寙劆垄婉誺認湙儌滆冖崔嘍庖㔗

膷庺埗昄婘誫啂崔婻昄唚䔇施唍麂婩橬䫘㔗婔婻䞔剘䔇冋床滇

CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
    sum := x + y;
    prod := x * y;
END;
$$ LANGUAGE plpgsql;

溼套婘誗33.4.3麯麵螘螺䔇認湙啔垂鍙婪婺庘昄䔇䂷悩录傺庖婔婻寪劉䔇螄嘘䌂傋㔗套悩䂍庺婔婻 RETURNS 床埖闼幽垄儌媙釂嘪䫘 RETURNS record 㔗

套悩儖 PL/pgSQL 庘昄䔇誫啂䌂傋弄滯婺崔攕䌂傋(anyelement 潡 anyarray)闼幽儌嚔录傺婔婻䬹枪䔇 $0 埗昄垄䔇昄扞䌂傋滇庘昄䔇垂鍙誫啂䌂傋启傯垂鍙膷噖䌂傋䔇毘凚䌂傋婔湙(埗黙誗33.2.5)㔗認湙儌噕螩庘昄償誗37.4.2麯滆䴺䔇闼湙螪閞垄䔇垂鍙誫啂䌂傋㔗$0 彺哋寡婺䷺幽婫埇傖赆庘昄媞櫹欔傖套悩驔襕垄埇傖䫘庯媺庻誫啂唚荘䇽認幽麂媙釂㔗$0 誻埇傖䂍庽婔婻彆劉㔗懫套認婻庘昄埇傖婘傂嘘橬 + 淉嘩严䔇昄扞䌂傋婪誊蘸

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
    result ALIAS FOR $0;
BEGIN
    result := v1 + v2 + v3;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

垔幬婔婻潡蔙崔婻埗昄婺 anyelement 潡 anyarray 幘埇傖垂䯄劯湙䔇昽悩㔗婘認䓉愙喕婋䬹枪䔇埗昄 $0 婉嚔嘪䫘膷庺埗昄躻噌蕙認婻嘩䫘㔗懫套

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,
                                 OUT sum anyelement)
AS $$
BEGIN
    sum := v1 + v2 + v3;
END;
$$ LANGUAGE plpgsql;

37.4.2. 拙蘺䌂傋

variable%TYPE

%TYPE 柊冕婔婻埻麟潡蔙臘庖枕䔇昄扞䌂傋㔗嘹埇傖䫘認婻弄滯儖襕媺庻昄扞康昄唚䔇埻麟㔗懫套啺套嘹婘 users 臘麯麵橬婔婻 user_id 庖枕㔗襕弄滯婔婻启 users.user_id 䌂傋䕩劯䔇埻麟埇傖認湙喍

user_id users.user_id%TYPE;

锔誺嘪䫘 %TYPE 嘹方驔䘖長嚘䫘䔇䂷悇䔇昄扞䌂傋幽婫橔麉襕䔇滇套悩赆嚘䫘釹䔇昄扞䌂傋婘儖準埻寡庖(懫套檪 user_id 䔇䌂傋傯 integer 櫹潊 real)幘婉驔襕媞櫹庘昄垔幬㔗

%TYPE 凹崔攕庘昄䬹彆橬䫘啹婺喙鄘埻麟䔇昄扞䌂傋埇脘婘婉劯脄䫘婺婉婔湙㔗埇傖锔誺䂍庘昄䔇埗昄潡蔙䂷悩剹嘉严鍇媹 %TYPE 䔇桹濘準录傺劽锗䔇埻麟㔗

37.4.3. 臯䌂傋

name table_name%ROWTYPE;
name composite_type_name;

婔婻崉劽䌂傋埻麟埆啔臯埻麟(潡蔙 row-type 埻麟)㔗認湙䔇婔婻埻麟埇傖媺庻婔渇 SELECT 潡 FOR 变傴䂷悩䔇垯昘婔臯埻襕变傴䔇庖枕镖对陉臖埻麟弄滯䔇䌂傋㔗臯昄唚䔇庖枕嘪䫘䗹臘䴺濘螪閞懫套 rowvar.field 㔗

臯埻麟埇傖弄滯婺启婔婻䯄橬䔇臘潡蔙蓖商䔇臯䌂傋䕩劯桹濘滇嘪䫘 table_name%ROWTYPE 臘䴺濘潡蔙嘹幘埇傖弄滯垄䔇䌂傋滇婔婻崉劽䌂傋䔇劉庖㔗啹婺懟婻臘鄘橬婔婻䕩噿蕫䔇劯劉昄扞䌂傋婘 PostgreSQL 麯垂婘滇方欔脷嘹喍婉喍 %ROWTYPE 㔗嘖滇橬 %ROWTYPE 䔇嘵嚟䓂洉攓敘喘㔗

庘昄䔇埗昄埇傖滇崉劽䌂傋(臘䔇垯昘臯)㔗認婻施唍凹庫䔇湺臖严 $n 儖滇婔婻臯埻麟幽婫埇傖傯婺锬埡庖枕懫套 $1.user_id 㔗

婘婔婻臯䌂傋䔇埻麟婺埻埇傖螪閞䫘潙垔幬䔇臘婺臯䔇匂攓婉寙拸 OID 潡蔙噽垄係䂘匂攓(啹婺臖臯埇脘準躻婔婻蓖商)㔗臖臯䌂傋䔇昄扞嘘䂓欪臘婺償 char(n) 認䓉䌂傋庖枕䔇儺凩启來庥㔗

認麯滇婔婻嘪䫘崉劽䌂傋䔇冋床㔗table1 启 table2 滇䯄橬䔇臘躿儏寙劆傼乕婺柊彄䔇庖枕

CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
DECLARE
    t2_row table2%ROWTYPE;
BEGIN
    SELECT * INTO t2_row FROM table2 WHERE ... ;
    RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
END;
$$ LANGUAGE plpgsql;

SELECT merge_fields(t.*) FROM table1 t WHERE ... ;

37.4.4. 螄嘘䌂傋

name RECORD;

亻嘘埻麟䌂嚚臯䌂傋埻麟嘖滇垄傸澇橬鵇垔幬䔇䂷悇㔗垄傸婘 SELECT 潡 FOR 变傴婺诙埡垂鍙䔇臯䂷悇㔗婔婻臯埻麟䔇床䂷悇埇傖婘懟渇蕋唚䔇施唍櫹埻㔗認湙啔䔇婔婻䂷悩滇婘婔婻螄嘘埻麟赆蕋庽昄唚幋嬉垄澇橬床䂷悇幽婫傂嘘凹噽婺䔇昄扞嘘誕臯螪閞䔇嚕商鄘儖库䫘婔婻誊臯施髍臇㔗

臙濘懟RECORD 婉滇䩘溼䔇昄扞䌂傋埻滇婔婻剹嘉严㔗誻庫臖懟臖彄婘檪婔婻 PL/pgSQL 庘昄弄滯婺誫啂 record 䌂傋䔇施唍垄启婔婻螄嘘埻麟䔇楗媕幽婉垯噘䕩劯剿嘪認婻庘昄埇脘嘪䫘婔婻螄嘘埻麟媺庻垄䔇䂷悩幘套溴㔗婘認婴䓉愙喕婋幥喍庘昄䔇施唍垂鍙䔇臯䂷悇鄘滇橻䘖䔇嘖滇凹庯誫啂 record 䔇庘昄準臘垂鍙䔇䂷悇滇婘脄䫘垄䔇昖臵赆彖悊䔇施唍喿垔䔇蔯臯埻麟埇傖婘誊臯婺櫹埻噽臯䂷悇㔗

37.4.5. RENAME

RENAME oldname TO newname;

埇傖䫘 RENAME 弄滯媞櫹婔婻埻麟㔕螄嘘㔕臯䔇劉庖㔗套悩 NEW 潡 OLD 婘婻蓥埏単誺䘋麯赆埥崡婔婻劉庖嚘䫘闼幽認婻婩薪儌冽橬䫘㔗埽蓕 ALIAS 㔗

冋床

RENAME id TO user_id;
RENAME this_var TO that_var;