38.5. 婘 PL/Tcl 麯螪閞昄扞康

欓臯婔婻傖庖严婾嘵嚟䂍庺䔇 SQL 昖臵㔗昖臵婺䔇髍臇嚔凚躘檕庺婔婻髍臇㔗劥彍spi_exec 䔇誫啂唚滇变傴崇䊖䔇臯昄(锬庺㔕某噖㔕敘桄㔕役鍴)套悩臖变傴滇婔婻媘脘攓臺埖彍誫啂镽㔗埥崡套悩昖臵滇婔溇 SELECT 臺埖闼幽锬庺䔇庖枕毬䙓婋麵柟誄䔇桹濘櫆婘 Tcl 埻麟婺㔗

埇锬䔇 -count 唚只臬 spi_exec 婘臖昖臵婺崇䊖䔇橔崓䔇臯昄㔗噽昽悩启檪昖臵螆䘞婺婔婻橩湺䇽劯臘 FETCH n 滇婔湙䔇㔗

套悩昖臵滇婔婻 SELECT 臺埖闼幽噽䂷悩彖䔇昄唚儖櫆婘䫘劇庖枕劉变劉䔇 Tcl 埻麟婺㔗套悩䂍庺庖 -array 锬釹闼幽庖枕唚儖櫆彄認婻变劉䔇䕩噿昄䂇婺庖枕劉䫘啔昄䂇䘵嚘㔗

套悩昖臵滇 SELECT 臺埖幽婫澇橬䂍庺 loop-body 臔橸闼幽埻橬䂷悩䔇崘婔臯嚔庻嗘彄 Tcl 埻麟婺套悩誻橬噽垄臯䔇臺儖嚔赆媘䘖㔗套悩昖臵澇橬誫啂傂嘘臯闼幽婉嚔庻嗘傔幽昄扞(認婻愙喕埇傖锔誺演昖 spi_exec 䔇䂷悩準彴桺)㔗懫套

spi_exec "SELECT count(*) AS cnt FROM pg_proc"

儖螆䘞 Tcl 埻麟 $cnt 螆婺係䂘臘 pg_proc 婺䔇臯昄㔗

套悩䂍庺庖埇锬䔇 loop-body 埗昄闼幽垄儌滇婔償枕 Tcl 臔橸垄嚔婺 SELECT 䂷悩婺䔇懟婔臯欓臯婔渇(濘懟套悩䂍庺䔇昖臵婉滇 SELECT闼幽媘䘖 loop-body)㔗婘懟渇誺傼幋嬉嘷嬉臯䔇庖枕䔇昄唚鄘庻嗘彄 Tcl 埻麟婺寂庖㔗懫套

spi_exec -array C "SELECT * FROM pg_class" {
    elog DEBUG "have table $C(relname)"
}

儖婺 pg_class 䔇懟婔臯欷剄婔臯斖媖媇敇㔗認婻䬹攓启噽垄 Tcl 冻䯇悇锹䔇誊啔桹嚟䌂嚚䬹彆滇 continue 启 break 婘冻䯇嘷婺䔇嘩䫘启广婩滇婔湙䔇㔗

套悩婔婻昖臵䂷悩䔇昊婻庖枕滇 NULL 闼幽噽䕞湺埻麟儌滇"unset"蔯婉嚔螆䘞婪傔幽婩薪㔗

婺劯麵䔇欓臯庖崺幽媺庻婔婻昖臵蓇彐㔗媺庻䔇蓇彐䔇䫘变橘儌滇嘷嬉嚔臺䔇䫘变橘㔗

昖臵埇傖嘪䫘埗昄認底埗昄滇蓇彐垂鍙欓臯䔇施唍柊冕䔇昄唚䔇剹嘉严㔗婘昖臵庖严婾麯䫘严埙 $1 ... $n 嚘䫘劇婻埗昄㔗套悩昖臵嘪䫘庖埗昄闼幽埗昄䌂傋劉媙驔傖婔婻 Tcl 彖臘䔇嘵嚟䂍庺㔗套悩澇橬嘪䫘埗昄闼幽䂍 typelist 喍婔婻䷺彖臘㔗䕞嬉埗昄䌂傋媙驔启 pg_type 麯滆䴺䔇喙鄘䌂傋劉婔湙懫套滇 int4 蔯婉滇 integer 㔗

spi_prepare 䔇誫啂唚滇婔婻埇傖婘锟劯䔇 spi_execp 脄䫘婺嘪䫘䔇昖臵 ID 㔗埗黙 spi_execp 诙埡冋床㔗

欓臯婔婻嬉麵䫘 spi_prepare 庖崺䔇昖臵㔗queryid 滇 spi_prepare 誫啂䔇 ID 㔗套悩臖昖臵嚘䫘庖埗昄闼幽媙驔柊冕婔婻 value-list 認滇婔婻 Tcl 彖臘麯麵寙劆闼底埗昄䔇垂鍙昄唚㔗認婻彖臘䔇阪庥媙驔启嬉麵䂍 spi_prepare 柊冕䔇埗昄䌂傋彖臘䔇阪庥婔湙阪㔗套悩昖臵澇橬埗昄闼幽䩕䘖 value-list 㔗

-nulls 埇锬䔇昄唚滇婔婻䷺䍘庖严婾启庖严 'n' 只臬 spi_execp 巻底埗昄滇 NULL 㔗套悩䂍庺闼幽垄媙驔启 value-list 䔇阪庥䕩劯㔗套悩澇橬䂍庺闼幽欔橬埗昄唚鄘滇麂 NULL 㔗

鍴庖昖臵埪噽埗昄弄滯䔇桹嚟幋崡spi_execp 䔇嘪䫘桹濘嘺橸婪启 spi_exec 婔湙㔗-count, -array, loop-body 锬釹鄘滇婔湙䔇䂷悩昄唚幘婔湙㔗

婋麵滇婔婻嘪䫘鵇崺蓇彐䔇 PL/Tcl 庘昄䔇冋床

CREATE FUNCTION t1_count(integer, integer) RETURNS integer AS $$
    if {![ info exists GD(plan) ]} {
        # prepare the saved plan on the first call
        set GD(plan) [ spi_prepare \
                "SELECT count(*) AS cnt FROM t1 WHERE num >= \$1 AND num <= \$2" \
                [ list int4 int4 ] ]
    }
    spi_execp -count 1 $GD(plan) [ list $1 $2 ]
    return $cnt
$$ LANGUAGE pltcl;

驔襕婘䂍 spi_prepare 䔇昖臵庖严婾麯櫆埉桩溹傖䇞媺 $n 湺螄嚔寘湙嚹锐䂍 spi_prepare 蔯婉滇赆 Tcl 䔇埻麟傼扵敪扵毬㔗

套悩臖昖臵滇剘臯 INSERT 幽婫赆媞櫹䔇臘寙劆 OID 彍誫啂橔劯䔇 spi_exec 潡 spi_execp 昖臵某噖䔇臯䔇 OID 㔗套悩婉滇儖冖彄镽㔗

婘䂍庺䔇庖严婾麯儖欔䫌剘嚘埙启埉桩溹庖严崉彽潊埯傘㔗垄埇傖䫘庯垬噘婄崇䊖闼底襕膷噖彄 spi_exec 潡 spi_prepare 婺䔇 SQL 变傴婺䔇嚘埙寙啘庖严婾㔗懫套啺套婔婻 SQL 变傴䩋蕙準償認湙

"SELECT '$val' AS ret"

認麯䔇 Tcl 埻麟 val 垂鍙婪寙劆 doesn't 㔗認湙橔劯䔇变傴庖严婾嚔滇認湙

SELECT 'doesn't' AS ret

蔯認婻庖严婾婘 spi_exec 潡 spi_prepare 䔇施唍嚔凚躘婔婻彖悊髍臇㔗婺庖脘噖嘩溼婩柊庴䔇变傴庫臖寙劆

SELECT 'doesn''t' AS ret

婘 PL/Tcl 婺埇傖認湙悇锹

"SELECT '[ quote $val ]' AS ret"

spi_execp 䔇婔婻嚻䗹滇嘹婉驔襕償認湙嚘埙寙啘埗昄唚啹婺埗昄䂺婉嚔嘷啔 SQL 昖臵庖严婾䔇婔鄘彖赆彖悊㔗

埏庺婔婻斖媖潡蔙髍臇潽敇㔗埇脘䔇亓彆滇 DEBUG, LOG, INFO, NOTICE, WARNING, ERROR, FATAL 㔗ERROR 檕庺婔婻髍臇溇傽臖臯昄誕婔準䔇欓臯儖婺溵劯施锔庺嘷嬉庋媇㔗FATAL 锔庺嘷嬉庋媇幽婫凚躘嘷嬉嚔臺噿閺(埇脘婘 PL/Tcl 庘昄麯澇橬傔幽䊖䫌嘪䫘認婻髍臇亓彆柊冕垄婂襕滇婺庖垯昘)㔗昊婻嚻噽亓彆䔇媇敇滇檖只䂍垵潙䆇誻滇喍彄橉媇単斖媖誻滇婴婻鄘啔滇䫌 log_min_messages 启 client_min_messages 陉䘞埻麟毓彽䔇㔗埗黙䆹17诙埡敘崔䂖誗㔗