PostgreSQL 8.2.3 婺桺桺懼
劯锔媆锔䆹37. PL/pgSQL - SQL 誺䘋臺蘔媆誕嬉誕

37.7. 毓彽䂷悇

毓彽䂷悇埇脘滇 PL/pgSQL 婺橔橬䫘䔇(傖埪橔麉襕)䔇鄘彖庖㔗彷䫘 PL/pgSQL 䔇毓彽䂷悇嘹埇傖傖麂婩䕕昂蔯婫嚺崓䔇桹濘淉井 PostgreSQL 䔇昄扞㔗

37.7.1. 傯庘昄誫啂

橬婴婻变傴埇傖䫘準傯庘昄婺誫啂昄扞RETURN 启 RETURN NEXT 㔗

37.7.1.1. RETURN

RETURN expression;

婥臘膆嚟䔇 RETURN 䫘庯䂽溵庘昄幽檪 expression䔇唚誫啂䂍脄䫘蔙㔗認䓉嘵嚟䫘庯婉誫啂镖劽䔇 PL/pgSQL 庘昄㔗

套悩誫啂湺麟䌂傋闼幽埇傖嘪䫘傂嘘臘膆嚟㔗臘膆嚟䔇䌂傋儖赆躻媘蘸扵潊庘昄䔇誫啂䌂傋儌償婘蕋唚婺柟誄䔇闼湙㔗襕誫啂婔婻崉劽(臯)昄唚嘹媙釂喍婔婻螄嘘潡蔙臯埻麟䔇 expression 㔗

套悩嘹弄滯庘昄婥膷庺埗昄闼幽儌埻驔襕喍方臘膆嚟䔇 RETURN 㔗闼幽膷庺埗昄埻麟䔇嘷嬉唚儖赆誫啂㔗

套悩嘹弄滯庘昄誫啂 void 闼幽婔婻 RETURN 臺埖埇傖䫘庯柊嬉锔庺庘昄嘖滇婉襕婘 RETURN 劯麵喍婔婻臘膆嚟㔗

婔婻庘昄䔇誫啂唚婉脘滇橻垔幬㔗套悩毓彽彄膆庖庘昄橔釽北䔇庖蔯澇橬䵄彄婔婻 RETURN 臺埖闼幽垄儌嚔埏䫘婔婻髍臇㔗婉誺認婻鍊彽婉锗䫘庯婥膷庺埗昄䔇庘昄傖埪闼底誫啂 void 䔇庘昄㔗婘認底冋床麯套悩釽北䔇庖䂷溘彍躻媘欓臯婔婻 RETURN 臺埖㔗

37.7.1.2. RETURN NEXT

RETURN NEXT expression;

套悩婔婻 PL/pgSQL 庘昄弄滯婺誫啂 SETOF sometype 闼幽镕冻䔇誺䘋彍䘖橬婉劯㔗婘認䓉愙喕婋襕誫啂䔇䋸䆋釹滇婘 RETURN NEXT 变傴麯弄滯䔇䇽劯橔劯橬婔婻婉婥埗昄䔇 RETURN 变傴䫘庯只臬認婻庘昄噾䂟垯潊欓臯庖㔗RETURN NEXT 埇傖䫘庯湺麟启崉劽昄扞䌂傋凹庯崉劽䌂傋儖誫啂婔婻垯昘䔇䂷悩"臘"㔗

RETURN NEXT 垂鍙婪幽婉傯庘昄婺誫啂垄埻滇䞔剘婄檪臘膆嚟䔇唚媺庻蕙準㔗䇽劯欓臯䂓䂺欓臯 PL/pgSQL 庘昄麯䔇婋婔溇臺埖㔗锟五劯䂓䔇 RETURN NEXT 变傴䔇欓臯䂷悩镖儌傺䆋蕙準庖㔗橔劯婔婻 RETURN 庫臖澇橬埗昄垄凚躘毓彽锔庺臖庘昄(潡蔙嘹埇傖䞔剘婄螷毓彽彄膆庘昄䔇䂷儆)㔗

套悩嘹弄滯庘昄婥橬膷庺埗昄闼幽儌埻驔襕喍婉婥臘膆嚟䔇 RETURN NEXT 㔗膷庺埗昄䔇嘷嬉唚儖赆媺庻䫘庯橔䂽誫啂㔗臙濘懟套悩橬崔婻膷庺埗昄懫套弄滯庘昄婺誫啂 SETOF record 潡蔙滇婘埻橬婔婻䌂傋婺 sometype 䔇膷庺埗昄施弄滯婺 SETOF sometype 認湙欉脘录傺婔婻婥橬膷庺埗昄䔇誫啂镖劽䔇庘昄㔗

嘪䫘 RETURN NEXT 䔇庘昄庫臖毬䙓婋麵䔇鼯湚脄䫘

SELECT * FROM some_func();

幘儌滇臘認婻庘昄䫘啔 FROM 床埖麯麵䔇婔婻臘昄扞溊㔗

㔊濘懟㔏䕞嬉䔇 PL/pgSQL 䔇 RETURN NEXT 垂䯄婘傯庘昄誫啂幋嬉檪昘婻䂷悩镖鄘媺庻蕙準儌償婪麵柟誄䔇闼湙㔗認懟叿五套悩婔婻 PL/pgSQL 庘昄䫘潊婔婻麂婩崓䔇䂷悩镖攓脘埇脘嚔冽噞昄扞儖赆喍彄伕䕻婪傖镪噉喙庻蔖儘嘖滇庘昄婘垯潊昘婻䂷悩镖䔇䫘潊幋嬉婉嚔锔庺㔗儖準䔇 PL/pgSQL 䬽橸埇脘嚔噕螩䫘潙垔幬澇橬認湙鍊彽䔇誫啂镖劽䔇庘昄㔗䕞嬉昄扞嚔哋劏伕䕻麯喍䔇施彂滇䫌陉䘞埻麟 work_mem 毓彽䔇㔗拖橬轿崘喙庻䔇䞇䊖叻套悩愿婘喙庻麯庻嗘敘崓䔇䂷悩镖彍埇傖蔄荏檪認婻埗昄嵂崓婔底㔗

37.7.2. 溇傽

IF 臺埖螷嘹埇傖湹扞昊䓉溇傽欓臯变傴㔗PL/pgSQL 橬庫䓉嘵嚟䔇 IF

37.7.2.1. IF-THEN

IF boolean-expression THEN
    statements
END IF;

IF-THEN 臺埖滇 IF 䔇橔䞔剘嘵嚟㔗套悩溇傽婺䩘婘 THEN 启 END IF 幋閘䔇臺埖儖赆欓臯㔗劥彍儖媘䘖垄傸㔗

冋床

IF v_user_id <> 0 THEN
    UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;

37.7.2.2. IF-THEN-ELSE

IF boolean-expression THEN
    statements
ELSE
    statements
END IF;

IF-THEN-ELSE 臺埖嵂媹庖 IF-THEN 䔇彖櫇螷嘹埇傖弄滯婘溇傽婺啺䔇施唍欓臯䔇臺埖㔗

冋床

IF parentid IS NULL OR parentid = ''
THEN
    RETURN fullname;
ELSE
    RETURN hp_true_filename(parentid) || '/' || fullname;
END IF;
IF v_count > 0 THEN 
    INSERT INTO users_count (count) VALUES (v_count);
    RETURN 't';
ELSE
    RETURN 'f';
END IF;

37.7.2.3. IF-THEN-ELSE IF

IF 臺埖埇傖償婋麵䔇冋床闼湙啯喖

IF demo_row.sex = 'm' THEN
    pretty_sex := 'man';
ELSE
    IF demo_row.sex = 'f' THEN
        pretty_sex := 'woman';
    END IF;
END IF;

認䓉嘵嚟垂鍙婪儌滇婘埥崡婔婻 IF 臺埖䔇 ELSE 鄘彖啯喖庖埥婔婻 IF 臺埖㔗啹溴嘹驔襕婔婻 END IF 臺埖䂍懟婻啯喖䔇 IF 埥崡誻襕婔婻䂍佽 IF-ELSE 䫘㔗認幽幾滇埇傖䔇嘖滇套悩橬崻崔唍锬釹驔襕演昖闼幽儌嚔埻冖冽幟叿㔗啹溴橬婋麵䔇嘵嚟㔗

37.7.2.4. IF-THEN-ELSIF-ELSE

IF boolean-expression THEN
    statements
[ELSIF boolean-expression THEN
    statements
[ELSIF boolean-expression THEN
    statements
    ...]]
[ELSE
    statements]
END IF;

IF-THEN-ELSIF-ELSE 柊冕庖婔䓉敘桹冪䔇桹濘䫘庯婘婔溇臺埖婺演昖螩崔唍锬溇傽㔗嘵嚟婪垄启啯喖䔇 IF-THEN-ELSE-IF-THEN 变傴䕩劯嘖滇埻驔襕婔婻 END IF 㔗

婔婻冋床

IF number = 0 THEN
    result := 'zero';
ELSIF number > 0 THEN 
    result := 'positive';
ELSIF number < 0 THEN
    result := 'negative';
ELSE
    -- hmm, the only other possibility is that number is null
    result := 'NULL';
END IF;

37.7.2.5. IF-THEN-ELSEIF-ELSE

ELSEIF 滇 ELSIF 䔇彆劉㔗

37.7.3. 䞔剘冻䯇

嘪䫘 LOOP, EXIT, CONTINUE, WHILE, FOR 臺埖埇傖毓彽 PL/pgSQL 庘昄麉崉婔係彖变傴㔗

37.7.3.1. LOOP

[<<label>>]
LOOP
    statements
END LOOP [label];

LOOP 垔幬婔婻方溇傽䔇冻䯇方鍊冻䯇䕘彄䫌 EXIT 潡 RETURN 臺埖䂽溵㔗埇锬䔇 label 埇傖䫌 EXIT 启 CONTINUE 臺埖嘪䫘䫘庯婘啯喖冻䯇婺弄滯庫臖庫䫘庯巻婔北冻䯇㔗

37.7.3.2. EXIT

EXIT [label] [WHEN expression];

套悩澇橬䂍庺 label 闼幽锔庺橔喙北䔇冻䯇䇽劯欓臯虘婘 END LOOP 劯麵䔇臺埖㔗套悩䂍庺 label 闼幽垄媙釂滇嘷嬉潡蔙敘醻北䔇啯喖冻䯇庖潡蔙臺埖庖䔇湺了㔗䇽劯臖变劉庖潡蔙冻䯇儌嚔䂽溵蔯毓彽芘彄凹庫冻䯇/庖䔇 END 臺埖劯麵䔇臺埖婪㔗

套悩弄滯庖 WHEN 冻䯇锔庺埻橬婘 expression 婺䩘䔇施唍欉埏䫘劥彍毓彽嚔芘彄 EXIT 劯麵䔇臺埖婪㔗

EXIT 埇傖䫘庯婘欔橬䔇冻䯇䌂傋婺垄幽婉備備鍊彽庯婘方溇傽冻䯇婺嘪䫘㔗婘启 BEGIN 庖婔蕙嘪䫘䔇施唍EXIT 檪毓彽庴䂍庖䂷溘劯䔇婋婔婻臺埖㔗

冋床

LOOP
    -- 婔底螇䞖
    IF count > 0 THEN
        EXIT;  -- 锔庺冻䯇
    END IF;
END LOOP;

LOOP
    -- 婔底螇䞖
    EXIT WHEN count > 0;  -- same result as previous example
END LOOP;

BEGIN
    -- 婔底螇䞖
    IF stocks > 100000 THEN
        EXIT;  -- 凚躘傯 BEGIN 庖麯锔庺
    END IF;
END;

37.7.3.3. CONTINUE

CONTINUE [label] [WHEN expression];

套悩澇橬䂍庺 label 闼幽儌嚔哋橔喙北冻䯇䔇婋婔渇欓臯㔗幘儌滇臘毓彽嚹锐啂䂍冻䯇毓彽臘膆嚟(套悩橬)䇽劯麉桄螇䞖冻䯇嘷㔗套悩庺䯄庖 label 垄弄滯剿儖䂓䂺欓臯䔇冻䯇䔇湺了㔗

套悩弄滯庖 WHEN 闼幽冻䯇䔇婋婔渇欓臯埻橬婘 expression 婺䩘䔇愙喕婋欉誕臯㔗劥彍毓彽嚹锐䂍 CONTINUE 劯麵䔇臺埖㔗

CONTINUE 埇傖䫘庯欔橬䌂傋䔇冻䯇垄幽婉備備鍊庯方溇傽冻䯇㔗

冋床

LOOP
    -- 婔底螇䞖
    EXIT WHEN count > 100;
    CONTINUE WHEN count < 50;
    -- 婔底婘 count 昄唚婘[50 .. 100]麯麵施唍䔇螇䞖
END LOOP;

37.7.3.4. WHILE

[<<label>>]
WHILE expression LOOP
    statements
END LOOP [label];

埻襕溇傽臘膆嚟婺䩘WHILE 臺埖儌嚔婉啩䔇婘婔係彖臺埖婪誕臯冻䯇溇傽滇婘懟渇誕噖冻䯇嘷䔇施唍演昖䔇㔗

For example:

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    -- 婔底螇䞖
END LOOP;

WHILE NOT boolean_expression LOOP
    -- 婔底螇䞖
END LOOP;

37.7.3.5. FOR (integer variant)

[<<label>>]
FOR name IN [REVERSE] expression .. expression [BY expression] LOOP
    statements
END LOOP [label];

認䓉嘵嚟䔇 FOR 凹婔垔评啘䔇昘昄誕臯誺傼䔇冻䯇㔗埻麟 name 嚔躻媘垔幬婺 integer 䌂傋幽婫埻婘冻䯇麯庻婘(傂嘘臖埻麟劉䔇䯄庻垔幬婘溴冻䯇喙鄘儖赆媘䘖)㔗䂍庺评啘婪婋䘯䔇婴婻臘膆嚟婘誕噖冻䯇䔇施唍螇䞖婔渇㔗BY 床埖毺垔誺傼準阪(䚺䩕婺 1)嘖套悩弄滯庖 REVERSE 準阪儖埻婺䕩庫䔇蘘唚㔗

婔底昘昄 FOR 冻䯇䔇冋床

FOR i IN 1..10 LOOP
    -- 婔底螇䞖
    RAISE NOTICE 'i is %', i;
END LOOP;

FOR i IN REVERSE 10..1 LOOP
    -- 婔底螇䞖
END LOOP;

FOR i IN REVERSE 10..1 BY 2 LOOP
    -- 婔底螇䞖
    RAISE NOTICE 'i is %', i;
END LOOP;

套悩婋䘯崓庯婪䘯(潡蔙滇婘 REVERSE 愙喕婋滇償庯)闼幽冻䯇嘷儖垯噘婉赆欓臯㔗蔯婫婉嚔檕庺傂嘘髍臇㔗

37.7.4. 镉寖变傴䂷悩

嘪䫘婉劯䌂傋䔇 FOR 冻䯇嘹埇傖镉寖婔婻变傴䔇䂷悩幽婫凹噽誕臯䕩庫䔇淉嘩㔗臺濘滇

[<<label>>]
FOR target IN query LOOP
    statements
END LOOP [label];

target 滇婔婻螄嘘埻麟㔕臯埻麟㔕锖埙彖锫䔇湺麟埻麟彖臘㔗target 赆誂䂺婉桺赆蕋庽欔橬準躻 query 䔇臯幽婫冻䯇嘷儖婺懟臯欓臯婔渇㔗婋麵滇婔婻冋床

CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
DECLARE
    mviews RECORD;
BEGIN
    PERFORM cs_log('Refreshing materialized views...');

    FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP

        -- 䯄婘"mviews"麯橬庖婔溇準躻 cs_materialized_views 䔇螄嘘 

        PERFORM cs_log('Refreshing materialized view ' || quote_ident(mviews.mv_name) || ' ...');
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
        EXECUTE 'INSERT INTO ' || quote_ident(mviews.mv_name) || ' ' || mviews.mv_query;
    END LOOP;

    PERFORM cs_log('Done refreshing materialized views.');
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

套悩冻䯇滇䫘婔婻 EXIT 臺埖䂽溵䔇闼幽婘冻䯇幋劯嘹傉䇽埇傖螪閞橔劯蕋唚䔇臯㔗

query 埇傖滇傂嘘誫啂臯䔇 SQL 变傴锔婩滇 SELECT 婉誺婥橬 RETURNING 床埖䔇 INSERT, UPDATE, DELETE 幘滇埇傖䔇婔底臩套 EXPLAIN 幋䌂䔇变傴幘埇傖㔗

FOR-IN-EXECUTE 臺埖滇镉寖欔橬臯䔇埥崡婔䓉桹濘

[<<label>>]
FOR target IN EXECUTE text_expression LOOP 
    statements
END LOOP [label];

認婻冋床䌂嚚嬉麵䔇嘵嚟埻婉誺溊昖臵臺埖弄滯婺庖婔婻庖严婾臘膆嚟認湙垄婘懟渇誕噖 FOR 冻䯇䔇施唍鄘嚔麉桄螇䞖启䫘潊欓臯螇彐㔗認湙儌噕螩䘋废叻婘婔婻鵇噽蓇彐喘庖䔇变傴欔诙冖䔇锘庥启婔婻媘攕变傴欔诙冖䔇䕕昂攓(儌償婔婻䞔剘䔇 EXECUTE 臺埖闼湙)幋閘誕臯锬拷㔗

㔊濘懟㔏 PL/pgSQL 彖悊単䕞嬉寺彖婴䓉䌂傋䔇 FOR 冻䯇(昘昄潡蔙誫啂螄嘘䔇)㔗桹濘滇演昖滇劥橬傂嘘 .. 庺䯄婘 IN 启 LOOP 幋閘䔇婖拸嚓幋崡㔗套悩澇橬䩋彄 .. 闼幽認婻冻䯇儌滇婘昄扞臯婪䔇冻䯇㔗套悩臇显庖 .. 儌冽埇脘嚔凚躘䌂嚚"loop variable of loop over rows must be a record or row variable or list of scalar variables"認湙䔇髍臇媇敇蔯婉滇䞔剘䔇臺濘髍臇㔗

37.7.5. 托诙髍臇

䚺䩕施婔婻婘 PL/pgSQL 庘昄麯埏䫘䔇髍臇锔庺庘昄䔇欓臯幽婫垂鍙婪噽变啘䔇庋媇幘嚔锔庺㔗嘹埇傖嘪䫘婔婻婥橬 EXCEPTION 床埖䔇 BEGIN 庖托诙髍臇幽婫傯婺敵崉㔗噽臺濘滇溼婩䔇 BEGIN 庖臺濘䔇婔婻欷匘

[<<label>>]
[DECLARE
    declarations]
BEGIN
    statements
EXCEPTION
    WHEN condition [OR condition ...] THEN
        handler_statements
    [WHEN condition [OR condition ...] THEN
          handler_statements
      ...]
END;

套悩澇橬埏䫘髍臇認䓉嘵嚟䔇庖埻滇䞔剘婄欓臯欔橬 statements 䇽劯蘸彄婋婔婻 END 幋劯䔇臺埖㔗嘖滇套悩婘 statements 喙鄘埏䫘庖婔婻髍臇彍凹 statements 䔇誕婔準崇䊖儖庘嚄䇽劯蘸彄 EXCEPTION 彖臘㔗係䂘抩䘵認婻彖臘凂欆对陉髍臇䔇丸婔婻 condition 㔗套悩欆彄对陉彍欓臯凹庫䔇 handler_statements 䇽劯蘸彄 END 幋劯䔇婋婔婻臺埖㔗套悩澇橬欆彄对陉臖髍臇儌嚔幪携庺寂儌喘償湹橸澇橬 EXCEPTION 床埖婔湙臖髍臇埇傖赆婔婻寙啘庖䫘 EXCEPTION 托诙套悩澇橬寙啘庖彍锔庺庘昄䔇崇䊖㔗

condition 䔇劉庖埇傖滇 鍇嘘A麯滆䴺䔇傂嘘劉庖㔗婔婻评䘘劉对陉傂懟臖评䘘麯䔇髍臇㔗䬹枪䔇溇傽劉 OTHERS 对陉鍴庖 QUERY_CANCELED 幋崡䔇欔橬髍臇䌂傋㔗埇傖䫘劉庖托诙 QUERY_CANCELED 婉誺锔婩滇婉滯捺䔇㔗溇傽劉滇崓償喍方噿䔇㔗

套悩婘锬婺䔇 handler_statements 麯埏䫘庖桄髍臇闼幽垄婉脘赆認婻 EXCEPTION 床埖托诙蔯滇嚹携庺寂㔗婔婻崡北䔇 EXCEPTION 床埖埇傖托诙垄㔗

套悩婔婻髍臇赆 EXCEPTION 托诙PL/pgSQL 庘昄䔇匔鄘埻麟媺毕髍臇埏䫘施䔇寘唚嘖滇欔橬臖庖婺愿啺寡婘昄扞康婺䔇䪽攕鄘啂悔㔗嘩婺婔婻冋床螷潏傸䩋䩋婋麵䬺桺

    INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
    BEGIN
        UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
        x := x + 1;
        y := x / 0;
    EXCEPTION
        WHEN division_by_zero THEN
            RAISE NOTICE 'caught division_by_zero';
            RETURN x;
    END;

嘷毓彽彄膆䂍 y 蕋唚䔇婄桹施垄嚔婥五婔婻 division_by_zero 髍臇崌蘖㔗認婻髍臇儖赆 EXCEPTION 床埖托诙㔗蔯婘 RETURN 臺埖麯誫啂䔇昄唚儖滇 x 䔇嵂麟唚㔗嘖滇婘臖庖幋嬉䔇 UPDATE 启 INSERT 儖婉嚔啂悔啹溴橔䂽䔇䂷悩滇昄扞康寙劆 Tom Jones 蔯婉滇 Joe Jones 㔗

㔊柊䴺㔏誕噖启锔庺婔婻寙劆 EXCEPTION 床埖䔇庖襕懫婉寙劆䔇庖嚔體崓䔇崔㔗啹溴婉媙襕䔇施唍婉襕嘪䫘 EXCEPTION 㔗

婘嚗婩崇䊖単婺SQLSTATE 埻麟寙劆檕庺髍臇凹庫䔇髍臇傼乕(埗蔄臘A-1诙埡埇脘䔇髍臇乕䔇彖臘)㔗SQLERRM 埻麟寙劆婯嚗婩噿蕫䔇髍臇媇敇㔗認底埻麟婘嚗婩崇䊖単崡麵滇橻垔幬䔇㔗

冋37-1. UPDATE/INSERT 嚗婩

認婻冋床湹扞嘪䫘嚗婩崇䊖単欓臯敄嘷䔇 UPDATE 潡 INSERT 㔗

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;

        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');
劯锔饡釕嬉誕
嘺橸臺埖婪婔亓橩湺