PostgreSQL 9.6.0 锟街诧拷
锟斤拷一页	锟斤拷一锟斤拷	锟斤拷 41. PL/pgSQL - SQL锟斤拷锟斤拷锟斤拷锟斤拷	锟斤拷一页

41.11. PL/pgSQL锟斤拷锟斤拷锟斤拷示

锟斤拷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 锟斤拷锟捷匡拷锟斤拷使锟斤拷撸锟斤拷锟斤拷芊锟斤拷锟皆癸拷锟斤拷锟斤拷锟皆的匡拷锟斤拷锟斤拷锟斤拷锟街癸拷锟竭碉拷一锟斤拷锟斤拷锟斤拷锟斤拷pgAdmin锟斤拷锟斤拷些锟斤拷锟斤拷通锟斤拷锟结供锟斤拷锟斤拷锟斤拷锟斤拷裕锟斤拷锟斤拷锟阶拷宓ワ拷锟斤拷锟斤拷约锟斤拷锟斤拷锟斤拷锟斤拷麓锟斤拷锟斤拷偷锟斤拷院锟斤拷锟斤拷锟�

41.11.1. 锟斤拷锟斤拷锟斤拷锟斤拷

一锟斤拷PL/pgSQL锟斤拷锟斤拷锟侥达拷锟斤拷锟斤拷一锟斤拷CREATE FUNCTION锟叫憋拷指锟斤拷为一锟斤拷锟街凤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟酵拷锟斤拷姆锟绞斤拷迅锟斤拷址锟斤拷锟叫达拷诘锟斤拷锟斤拷锟斤拷屑洌拷锟矫达拷煤锟斤拷锟斤拷锟斤拷械锟斤拷魏蔚锟斤拷锟斤拷哦锟斤拷锟斤拷氡凰达拷锟酵拷锟斤拷魏畏锟叫憋拷锟揭诧拷锟斤拷氡凰达拷锟斤拷俣锟绞癸拷锟斤拷锟阶拷锟斤拷址锟斤拷锟斤拷锓拷锟斤拷锟剿达拷锟斤拷锟斤拷锟斤拷锟叫碉拷锟竭筹拷锟斤拷锟斤拷锟斤拷锟节革拷锟斤拷锟接碉拷锟斤拷锟斤拷写锟斤拷锟斤拷锟斤拷锟斤拷全锟睫凤拷锟斤拷锟解，锟斤拷为锟斤拷锟斤拷锟斤拷追锟斤拷锟斤拷锟斤拷锟揭拷锟斤拷锟斤拷吒锟斤拷锟斤拷锟斤拷诘锟斤拷锟斤拷拧锟斤拷锟斤拷锟斤拷萍锟斤拷锟阶拷锟斤拷押锟斤拷锟斤拷锟叫达拷锟揭伙拷锟�"锟斤拷元锟斤拷锟斤拷"锟斤拷锟街凤拷锟斤拷锟斤拷锟斤拷锟斤拷 4.1.2.4 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷元锟斤拷锟矫凤拷锟斤拷锟叫ｏ拷锟斤拷硬锟斤拷锟揭达拷魏锟斤拷锟斤拷拧锟斤拷锟斤拷锟揭拷锟轿拷锟斤拷锟揭拷锟矫恳伙拷锟角讹拷锟窖★拷锟揭伙拷锟斤拷锟酵拷锟斤拷锟皆拷锟斤拷枚锟斤拷锟斤拷锟斤拷锟斤拷锟界，锟斤拷锟斤拷馨锟�CREATE FUNCTION锟斤拷锟斤拷写锟缴ｏ拷

CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$
          ....
$PROC$ LANGUAGE plpgsql;

锟斤拷锟斤拷锟斤拷锟芥，锟斤拷锟斤拷锟斤拷锟� SQL 锟斤拷锟斤拷锟斤拷为锟斤拷锟街凤拷锟斤拷使锟斤拷锟斤拷锟脚诧拷锟斤拷锟斤拷$$锟斤拷锟界定锟斤拷锟斤拷锟斤拷装锟斤拷锟街凤拷锟斤拷锟斤拷 SQL 锟斤拷锟斤拷片锟轿★拷锟斤拷锟斤拷锟斤拷锟揭拷锟斤拷冒锟斤拷锟�$$锟斤拷锟侥憋拷锟斤拷锟斤拷锟斤拷锟绞癸拷锟� $Q$ 锟饺等★拷

锟斤拷锟斤拷图锟斤拷展示锟斤拷锟斤拷写没锟斤拷锟斤拷元锟斤拷锟矫碉拷锟斤拷锟斤拷时锟斤拷要锟斤拷什么锟斤拷锟节斤拷之前锟斤拷锟斤拷元锟斤拷锟矫的达拷锟诫翻锟斤拷筛锟斤拷锟斤拷锟斤拷锟斤拷锟侥达拷锟斤拷时锟斤拷锟斤拷锟角伙拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷

1 锟斤拷锟斤拷锟斤拷

锟斤拷锟斤拷锟斤拷始锟酵斤拷锟斤拷锟斤拷锟斤拷锟藉，锟斤拷锟界：

CREATE FUNCTION foo() RETURNS integer AS '
          ....
' LANGUAGE plpgsql;

锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟矫的猴拷锟斤拷锟斤拷锟叫碉拷锟轿猴拷位锟矫ｏ拷锟斤拷锟斤拷锟斤拷锟斤拷锟缴对筹拷锟街★拷

2 锟斤拷锟斤拷锟斤拷

锟斤拷锟节猴拷锟斤拷锟斤拷锟节碉拷锟街凤拷锟斤拷锟斤拷锟斤拷锟界：

a_output := ''Blah'';
SELECT * FROM users WHERE f_name=''foobar'';

锟斤拷锟斤拷元锟斤拷锟矫凤拷锟斤拷锟叫ｏ拷锟斤拷只锟斤拷要写锟斤拷

a_output := 'Blah';
SELECT * FROM users WHERE f_name='foobar';

锟斤拷恰锟矫撅拷锟斤拷PL/pgSQL锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷谢峥达拷锟斤拷摹锟�

4 锟斤拷锟斤拷锟斤拷

锟斤拷锟斤拷锟节猴拷锟斤拷锟节碉拷一锟斤拷锟街凤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷要一锟斤拷锟斤拷锟斤拷锟斤拷时锟斤拷锟斤拷锟界：

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$$

要小锟斤拷锟斤拷锟斤拷锟斤拷围锟斤拷锟轿猴拷锟斤拷元锟斤拷锟矫讹拷锟斤拷锟斤拷锟街伙拷锟�$$锟斤拷

6 锟斤拷锟斤拷锟斤拷

锟斤拷锟节猴拷锟斤拷锟斤拷锟节碉拷一锟斤拷锟街凤拷锟斤拷锟叫碉拷一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷址锟斤拷锟斤拷锟斤拷锟侥┪诧拷锟斤拷冢锟斤拷锟斤拷纾�

a_output := a_output || '' AND name LIKE ''''foobar''''''

锟斤拷追锟接碉拷a_output锟斤拷值锟斤拷锟角ｏ拷 AND name LIKE 'foobar'锟斤拷

锟斤拷锟斤拷元锟斤拷锟矫凤拷锟斤拷锟叫ｏ拷锟斤拷锟斤拷桑锟�

a_output := a_output || $$ AND name LIKE 'foobar'$$

10 锟斤拷锟斤拷锟斤拷

锟斤拷锟斤拷锟斤拷锟斤拷一锟斤拷锟街凤拷锟斤拷锟斤拷锟斤拷锟斤拷占 8 锟斤拷锟斤拷锟脚ｏ拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷时锟斤拷锟斤拷锟斤拷岚わ拷鸥锟斤拷址锟斤拷锟斤拷锟斤拷锟斤拷锟侥┪诧拷锟斤拷锟斤拷锟� 2 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟叫匆伙拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷暮锟斤拷锟斤拷锟斤拷锟�锟斤拷 41-9锟叫ｏ拷锟斤拷锟姐将锟杰匡拷锟斤拷只锟斤拷要锟斤拷锟街★拷锟斤拷锟界：

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锟叫ｏ拷锟斤拷为锟斤拷使锟斤拷前锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟矫★拷

41.11.2. 锟斤拷锟斤拷谋锟斤拷锟绞憋拷锟斤拷

为锟剿革拷锟斤拷锟矫伙拷锟斤拷一些锟津单碉拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟轿ｏ拷锟街帮拷业锟斤拷锟斤拷牵锟� PL/PgSQL锟结供锟剿讹拷锟斤拷锟�锟斤拷锟�锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷时锟斤拷锟斤拷锟斤拷锟斤拷锟矫ｏ拷锟斤拷锟角匡拷锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟侥憋拷锟斤拷锟节间被锟斤拷锟斤拷锟斤拷锟斤拷 WARNING锟斤拷锟斤拷ERROR锟斤拷一锟斤拷锟窖撅拷锟秸碉拷锟斤拷 WARNING锟侥猴拷锟斤拷锟斤拷锟皆憋拷锟斤拷锟斤拷执锟叫讹拷锟斤拷锟斤拷锟斤拷锟斤拷锟揭伙拷锟斤拷锟斤拷锟较拷锟� 锟斤拷私锟斤拷锟斤拷锟斤拷锟揭伙拷锟斤拷锟斤拷锟斤拷目锟斤拷锟斤拷锟斤拷锟斤拷薪锟斤拷胁锟斤拷浴锟�

锟斤拷些锟斤拷锟斤拷募锟斤拷通锟斤拷锟斤拷锟矫憋拷锟斤拷plpgsql.extra_warnings 锟斤拷plpgsql.extra_errors锟斤拷锟矫ｏ拷锟斤拷锟斤拷前锟斤拷锟斤拷锟节撅拷锟斤拷锟斤拷锟斤拷锟斤拷锟� 锟节达拷锟斤拷锟斤拷锟竭讹拷锟斤拷锟皆憋拷锟斤拷锟斤拷为一锟斤拷锟矫讹拷锟脚分革拷锟侥硷拷锟斤拷斜锟斤拷锟� "none"锟斤拷锟斤拷"all"锟斤拷默锟斤拷值锟斤拷"none"锟斤拷锟斤拷前锟叫憋拷锟叫匡拷锟矫的硷拷锟街伙拷锟揭伙拷郑锟�

shadowed_variables: 锟斤拷锟揭伙拷锟斤拷锟斤拷锟斤拷欠锟斤拷诟锟斤拷锟斤拷锟揭伙拷锟街帮拷锟斤拷锟侥憋拷锟斤拷锟斤拷

锟斤拷锟斤拷锟斤拷锟斤拷锟秸故撅拷私锟�plpgsql.extra_warnings 锟斤拷锟斤拷为shadowed_variables锟斤拷效锟斤拷锟斤拷

SET plpgsql.extra_warnings TO 'shadowed_variables';

CREATE FUNCTION foo(f1 int) RETURNS int AS $$
DECLARE
f1 int;
BEGIN
RETURN f1;
END
$$ LANGUAGE plpgsql;
WARNING:  variable "f1" shadows a previously defined variable
LINE 3: f1 int;
        ^
CREATE FUNCTION

锟斤拷一页	锟斤拷始页	锟斤拷一页
PL/pgSQL锟斤拷锟节诧拷	锟斤拷一锟斤拷	锟斤拷Oracle PL/SQL 锟斤拷植