䫘 PL/pgSQL 啔嚔埏䔇婔婻喘桹濘滇䞔剘婄嘪䫘桺橸䚡膏単录傺庘昄䇽劯婘埥崡婔婻毓彽埄麯䫘 psql 媹蘘認底庘昄㔗套悩嘹䫘認䓉桹濘闼幽䫘 CREATE OR REPLACE FUNCTION 喍庘昄滇婻喘婂懟㔗認湙麉臂桺傽儌埇傖敘桄庘昄垔幬㔗懫套
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$
....
$$ LANGUAGE plpgsql;
婘誊臯 psql 䔇施唍埇傖䫘婋麵变傴媹蘘潡蔙麉蘘庘昄垔幬桺傽
\i filename.sql
䇽劯鷸婪埏庺 SQL 变傴敋臘臖庘昄㔗
埥崡婔婻嚔埏 PL/pgSQL 䘋废䔇喘桹濘滇嘪䫘婔䓉櫇毕誺䘋臺蘔嚔埏䔇 GUI 噖噙㔗懫套 PgAccess 嘷䇽誻橬噽垄䔇㔗認底噖噙锔婩柊冕庖婔底冽橬䫘䔇媘脘懫套锄锩剘嚘埙嘪冖麉傺启脄臘庘昄敘䞔剘京㔗
PL/pgSQL 庘昄䔇傼乕鄘滇婘 CREATE FUNCTION 麯傖婔婻庖严婾桺橸䔇桹嚟弄滯䔇㔗套悩嘹䫘婴膹寙啘剘嚘埙䔇婩蓇桹嚟喍庖严婾桺橸闼幽傂嘘庘昄嘷喙䔇剘嚘埙鄘媙釂喍埯傘䌂嚚䔇滇埉桩溹幘媙釂埯傘㔗埯傘嚘埙麂婩幟叿婘敘崉溗䔇婺劽婋傼乕埇脘嚔螷庺锆傖䊖蓼啹婺嘹冽垹滷埏䯄躻噌驔襕剪欷䫔躿敘崔䕩誂䔇嚘埙㔗傺螞嘹䫘"䆯噄严䘯垔"䔇庖严婾桺橸準喍庘昄嘷㔗(埗黙誗4.1.2.2)㔗嘪䫘䆯噄严䘯垔䔇施唍嘹傯婉驔襕凹傂嘘嚘埙喍埯傘埻驔襕婺懟北嚘埙寙啘啯喖锬拷婔婻婉劯䔇䆯噄严埙寙啘彖锫严剿埇㔗懫套嘹埇脘認幽喍 CREATE FUNCTION 变傴
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$
....
$PROC$ LANGUAGE plpgsql;
婘認婻庘昄嘷婺埇傖婘 SQL 变傴麯嘪䫘剘嚘埙寙啘桺橸庖严婾䫘 $$ 彖锫闼底 SQL 变傴䔇䬺桺㔗套悩嘹驔襕凹寙劆 $$ 䔇桺橸誕臯嚘埙寙啘埇傖嘪䫘 $Q$ 京京㔗
婋臘匘䴺庖婉嘪䫘䆯噄严䘯垔䔇施唍臖套嘘喍剘嚘埙㔗檪䆯噄严嚘埙幋嬉䔇嚘埙寙啘䔇傼乕蘸扵潊昊䓉埇傖䊖蓼䔇嘵嚟施庫臖嚔䫘冖婪㔗
嚔哋/䂷溘庘昄嘷懫套
CREATE FUNCTION foo() RETURNS integer AS '
....
' LANGUAGE plpgsql;
婘庘昄嘷喙鄘䔇傂嘘嘉䘞閞埙鄘媙釂潊凹庺䯄㔗
凹庯庘昄嘷喙䔇庖严婾桺橸懫套
a_output := ''Blah''; SELECT * FROM users WHERE f_name=''foobar'';
婘䆯噄严䘯垔䔇桹濘麯嘹埻襕喍
a_output := 'Blah'; SELECT * FROM users WHERE f_name='foobar';
婴䓉愙喕鄘滇 PL/pgSQL 彖悊単橘橕䩋彄䔇婩薪㔗
套悩嘹婘庘昄嘷婺䔇庖严婾麯麵驔襕婔婻剘嚘埙懫套
a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''
a_output 䔇唚儖滇 AND name LIKE 'foobar' AND xyz
嘪䫘䆯噄严䘯垔䔇桹濘庫臖認湙喍
a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$
濘懟認湙䔇䆯噄严䘯垔䔇彖锫严幽婉滇埻橬 $$
套悩婔婻婘庘昄嘷婺䔇庖严婾喙䔇剘嚘埙婯臖庖严婾婩麟䂷儆嬉劯䕩誂懫套
a_output := a_output || '' AND name LIKE ''''foobar''''''
a_output 䔇唚儖滇 AND name LIKE 'foobar'
䫘䆯噄严䘯垔䔇桹濘彍婺滇
a_output := a_output || $$ AND name LIKE 'foobar'$$
套悩嘹愿襕婘庖严婾婩麟麯橬婴婻剘嚘埙(垄傸婘婔蕙滇 8 婻庖)幽婫認婴婻剘嚘埙启臖庖严婾婩麟䔇䂷儆䕩誂(埽媹 2 婻)㔗埇脘埻橬婘喍婔婻䫘潊噽垄庘昄䔇庘昄䔇施唍償冋37-6麯闼湙欉驔襕認幽幾㔗懫套
a_output := a_output || '' if v_'' ||
referrer_keys.kind || '' like ''''''''''
|| referrer_keys.key_string || ''''''''''
then return '''''' || referrer_keys.referrer_type
|| ''''''; end if;'';
a_output 䔇唚儖滇
if v_... like ''...'' then return ''...''; end if;
嘪䫘䆯噄严䘯垔䔇桹濘庫臖認湙喍
a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$
|| referrer_keys.key_string || $$'
then return '$$ || referrer_keys.referrer_type
|| $$'; end if;$$;
啺螆潏傸埻驔襕婘 a_output 麯櫆剘嚘埙啹婺婘嘪䫘嬉垄嚔赆麉桄嚘埙寙啘㔗