33.4. 昖臵臺蘔(SQL)庘昄

CREATE FUNCTION clean_emp() RETURNS void AS '
    DELETE FROM emp
        WHERE salary < 0;
' LANGUAGE SQL;

SELECT clean_emp();

 clean_emp
-----------

(1 row)

INSERT INTO mytable VALUES ($1);

INSERT INTO $1 VALUES (42);

33.4.1. 嘺橸䌂傋婪䔇 SQL 庘昄

橔䞔剘䔇 SQL 庘昄埇脘澇橬埗昄幽婫誫啂婔婻嘺橸䌂傋懫套婔婻誫啂 integer 䔇庘昄

CREATE FUNCTION one() RETURNS integer AS $$
    SELECT 1 AS result;
$$ LANGUAGE SQL;

-- 埥崡婔䓉庖严婾桺橸䔇臺濘
CREATE FUNCTION one() RETURNS integer AS '
    SELECT 1 AS result;
' LANGUAGE SQL;

SELECT one();

 one
-----
   1

臙濘懟潏傸婘庘昄嘷麯麵垔幬庖婔婻庖枕彆劉(result)䫘庯庘昄䂷悩嘖滇認婻庖枕彆劉婘庘昄崡麵滇婉埇蓕䔇㔗啹溴䂷悩滇傖 one 蔯婉滇 result 婺湺了䔇㔗

垔幬婔婻毖埖嘺橸䌂傋啔埗昄䔇 SQL 庘昄庹幯婔湙䞔剘㔗婘婋麵䔇冋床麯臙濘懟潏傸婘庘昄婺滇套嘘嘪䫘 $1 启 $2 嚘䫘埗昄䔇㔗

CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
    SELECT $1 + $2;
$$ LANGUAGE SQL;

SELECT add_em(1, 2) AS answer;

 answer
--------
      3

婋麵滇婔婻敘橬䫘䔇庘昄潏傸埇傖䫘垄凹婔婻鷽臯婊埙啔欼渆媘嘩

CREATE FUNCTION tf1 (integer, numeric) RETURNS integer AS $$
    UPDATE bank 
        SET balance = balance - $2
        WHERE accountno = $1;
    SELECT 1;
$$ LANGUAGE SQL;

埇傖償婋麵認湙䫘認婻庘昄䂍婊潙 17 欼渆 $100.00 

SELECT tf1(17, 100.0);

垂鍙婪潏傸埇脘婯橕庘昄橬婔婻懫婩麟 1 敘橬䫘婔底䔇䂷悩㔗欔傖垂䫘䔇垔幬埇脘滇

CREATE FUNCTION tf1 (integer, numeric) RETURNS numeric AS $$
    UPDATE bank 
        SET balance = balance - $2
        WHERE accountno = $1;
    SELECT balance FROM bank WHERE accountno = $1;
$$ LANGUAGE SQL;

垄媞櫹嘍鵺幽誫啂桄䔇嘍鵺㔗

33.4.2. 崉劽䌂傋婪䔇 SQL 庘昄

嘷幥喍嘪䫘䫘崉劽䌂傋啔埗昄䔇庘昄施婉備襕弄滯驔襕巻婻埗昄(償婪麵嘪䫘 $1 启 $2 婔湙)蔯婫襕弄滯埗昄䔇庖枕(昄扞嘘)㔗懫套啺螆 emp 滇婔婻寙劆镺叻媇敇䔇臘幽婫啹溴幘滇臖臘懟臯䔇崉劽䌂傋䔇劉庖㔗婔婻螇䞖昊庺衻愘䪂䘻幋劯昄唚䔇 double_salary 庘昄

CREATE TABLE emp (
    name        text,
    salary      numeric,
    age         integer,
    cubicle     point
);

CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$
    SELECT $1.salary * 2 AS salary;
$$ LANGUAGE SQL;

SELECT name, double_salary(emp.*) AS dream
    FROM emp
    WHERE emp.cubicle ~= point '(2,1)';

 name | dream
------+-------
 Bill |  8400

臙濘懟認麯嘪䫘 $1.salary 臺濘锬拷埗昄臯昄唚䔇婔婻庖枕㔗誻襕濘懟 SELECT 变傴嘪䫘 * 臘䴺臖臘䔇昘婻嘷嬉臯嘩婺崉劽昄唚㔗臘麯麵䔇臯幘埇傖䫘臘劉庖嚘䫘償婋麵認湙

SELECT name, double_salary(emp) AS dream
    FROM emp
    WHERE emp.cubicle ~= point '(2,1)';

婉誺認婻䫘濘噾䂟庘嚄庖啹婺冽垹滷凚躘晙晖㔗

橬施唍䫘 ROW 悇锹単媘攕婄悇锹婔婻崉劽埗昄唚幘冽橬䫘㔗懫套潏傸埇傖脄誗嚹锐䂍庘昄䔇昄扞

SELECT name, double_salary(ROW(name, salary*1.1, age, cubicle)) AS dream
    FROM emp;

幘埇傖喍婔婻誫啂崉劽䌂傋䔇庘昄㔗婋麵滇婔婻埻誫啂婔臯䔇 emp 庘昄

CREATE FUNCTION new_emp() RETURNS emp AS $$
    SELECT text 'None' AS name,
        1000.0 AS salary,
        25 AS age,
        point '(2,2)' AS cubicle;
$$ LANGUAGE SQL;

婘認婻冋床婺潏傸䂍懟婻庖枕鄘蕋庽庖婔婻婩麟嘷䇽幘埇傖䫘傂嘘臘膆嚟準傼敪認底婩麟㔗

濘懟垔幬庘昄䔇婴婻麉襕閞鵻

• 锬拷彖臘䔇釺废媙釂启婯臖崉劽䌂傋䕩噿䔇臘婺庖枕䔇釺废垯噘婔湙㔗償婪麵闼湙䂍庖枕变劉滇启係䂘懆方噿係䔇㔗

• 嘹媙釂凹臘膆嚟誕臯䌂傋蘸扵傖对陉崉劽䌂傋䔇垔幬㔗劥彍嘹儖䩋彄婋麵䔇髍臇媇敇

  ERROR:  function declared to return emp returns varchar instead of text at column 1

埥崡婔婻垔幬劯湙庘昄䔇桹濘滇

CREATE FUNCTION new_emp() RETURNS emp AS $$
    SELECT ROW('None', 1000.0, 25, '(2,2)')::emp;
$$ LANGUAGE SQL;

認麯䔇 SELECT 埻誫啂凹庫崉劽䌂傋䔇婔婻剘䋸庖枕㔗婘認䓉愙喕婋認幽啔幽澇橬傂嘘喘崇嘖滇垄婘昊底婺劽滇婔婻冽喘䫘䔇婩薪㔗懫套驔襕锔誺脄䫘埥崡婔婻誫啂欔驔崉劽䌂傋昄唚䔇庘昄準螇䞖䂷悩㔗

埇傖䫘婴䓉桹濘䕘毖脄䫘認婻庘昄

SELECT new_emp();

         new_emp
--------------------------
 (None,1000.0,25,"(2,2)")

SELECT * FROM new_emp();

 name | salary | age | cubicle
------+--------+-----+---------
 None | 1000.0 |  25 | (2,2)

丸庯䓉桹濘婘誗33.4.4麯橬敘垯昘䔇柟誄㔗

婘嘪䫘婔婻誫啂崉劽䌂傋䔇庘昄施嘹埇傖䫘婋麵䔇臺濘傯䂷悩婺埻檘埡婔婻庖枕

SELECT (new_emp()).name;

 name
------
 None

媙釂䫘婔凹鵺崡䔇婖拸嚓黾溵彖悊単臇蓼㔗套悩䩕䘖認凹拸嚓儌嚔䩋蓕䌂嚚婋麵認湙䔇婩薪

SELECT new_emp().name;
ERROR:  syntax error at or near "." at character 17
LINE 1: SELECT new_emp().name;
                        ^

埥崡婔婻锬拷滇嘪䫘庘昄臘䴺濘檘埡庖枕㔗蓼麪認底閞鵻䔇䞔剘桹濘滇庴庐嘪䫘 attribute(table) 启 table.attribute 臘䴺濘㔗

SELECT name(new_emp());

 name
------
 None

-- 婪誄臺埖婯婋麵䔇認婻䕩劯
-- SELECT emp.name AS youngster FROM emp WHERE emp.age < 30;

SELECT name(emp) AS youngster FROM emp WHERE age(emp) < 30;

 youngster
-----------
 Sam
 Andy

誻橬婔婻嘪䫘庘昄誫啂崉劽䌂傋䔇愙喕滇檪䂷悩嚹锐䂍埥崡婔婻膷噖臖臯䌂傋䔇庘昄

CREATE FUNCTION getname(emp) RETURNS text AS $$
    SELECT $1.name;
$$ LANGUAGE SQL;

SELECT getname(new_emp());
 getname
---------
 None
(1 row)

誻埇傖檪誫啂崉劽䌂傋䔇庘昄嘷嘩婔婻臘庘昄嘪䫘套誗33.4.4欔誄㔗

33.4.3. 婥膷庺埗昄䔇庘昄

柟誄庘昄䔇䂷悩䔇埥崡婔䓉桹濘滇檪垄垔幬潊婥橬膷庺埗昄䔇庘昄

CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int)
AS 'SELECT $1 + $2'
LANGUAGE SQL;

SELECT add_em(3,7);
 add_em
--------
     10
(1 row)

認婻䬽橸启誗33.4.1麯麵䔇闼婻 add_em 䬽橸澇橬傔幽橸蘘䔇寺彆㔗膷庺埗昄䔇䩘溼備唚婘庯垄柊冕庖垔幬誫啂崔婻庖枕䔇庘昄䔇冪彷桹濘㔗懫套

CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int)
AS 'SELECT $1 + $2, $1 * $2'
LANGUAGE SQL;

 SELECT * FROM sum_n_product(11,42);
 sum | product
-----+---------
  53 |     462
(1 row)

認麯垂鍙埏䫘䔇庋愙滇潏傸婺庘昄䔇䂷悩录傺庖婔婻寪劉䔇崉劽䌂傋㔗婪麵䔇冋床启婋麵䔇冋床橬劯湙䔇橔䂽䂷悩

CREATE TYPE sum_prod AS (sum int, product int);

CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod
AS 'SELECT $1 + $2, $1 * $2'
LANGUAGE SQL;

婉誺婉䫘淉媄䋸䆋䔇崉劽䌂傋垔幬锔婩鄘嚔冽桹冪㔗

臙濘懟傯 SQL 麯脄䫘認底庘昄䔇施唍膷庺埗昄幽橻寙劆婘脄䫘埗昄彖臘麯㔗認滇啹婺 PostgreSQL 螴婺埻橬膷噖埗昄垔幬庘昄䔇脄䫘了劉㔗認幘懟叿五婘䌂嚚役鍴庘昄認湙䔇婺劽麯埻橬膷噖埗昄䞇䫘㔗潏傸埇傖䫘婋彖变傴幋婔役鍴婪誄庘昄

DROP FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int);
DROP FUNCTION sum_n_product (int, int);

埗昄埇傖湺螄婺 IN(䚺䩕), OUT, INOUT 㔗INOUT 埗昄劯施嘩婺膷噖埗昄(脄䫘埗昄彖臘䔇婔鄘彖)启膷庺埗昄(䂷悩螄嘘䌂傋䔇婔鄘彖)㔗

33.4.4. 嘩婺臘昄扞溊䔇 SQL 庘昄

欔橬 SQL 庘昄鄘埇傖婘昖臵䔇 FROM 床埖麯嘪䫘㔗嘖滇垄凹庯誫啂崉劽䌂傋䔇庘昄䬹彆橬䫘㔗套悩臖庘昄垔幬婺誫啂婔婻嘺橸䌂傋闼幽臘庘昄䫘潊婔婻剘庖枕臘㔗套悩臖庘昄垔幬婺誫啂婔婻崉劽䌂傋闼幽臖臘庘昄䫘潊婔婻臖崉劽䌂傋麯懟婻匂攓䂇潊䔇臯㔗

認麯滇婔婻冋床

CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'Joe');
INSERT INTO foo VALUES (1, 2, 'Ed');
INSERT INTO foo VALUES (2, 1, 'Mary');

CREATE FUNCTION getfoo(int) RETURNS foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT *, upper(fooname) FROM getfoo(1) AS t1;

 fooid | foosubid | fooname | upper
-------+----------+---------+-------
     1 |        1 | Joe     | JOE
(1 row)

溼套認婻冋床滆䴺䔇闼湙埇傖償凹写婔婻捞锔臘䔇庖枕婔湙凹写庘昄䔇䂷悩庖枕㔗

臙濘懟潏傸埻傯臖庘昄婺诙埡庖婔臯㔗認滇啹婺澇橬嘪䫘 SETOF 㔗認婻閞鵻婘婋婔誗螾誄㔗

33.4.5. 誫啂镖劽䔇 SQL 庘昄

套悩婔婻 SQL 庘昄弄滯婺誫啂 SETOF sometype 闼幽臖庘昄橔劯䔇 SELECT 昖臵婔䕘欓臯彄䂷溘幽婫垄膷庺䔇懟婔臯鄘赆嘷嘩臖䂷悩镖婺䔇婔婻噄䘹誫啂㔗

認婻䬹攓锔婩䫘庯檪庘昄櫆婘 FROM 床埖麯脄䫘㔗溴施臖庘昄誫啂䔇懟婔臯鄘潊婺昖臵埇蓕䔇臖臘䔇婔臯㔗懫套啺螆臘 foo 䔇喙垹启婪麵䕩劯闼幽

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

儖冖彄

 fooid | foosubid | fooname
-------+----------+---------
     1 |        1 | Joe
     1 |        2 | Ed
(2 rows)

䕞嬉誫啂镖劽䔇庘昄幘埇傖婘婔婻昖臵䔇锬拷彖臘麯脄䫘㔗凹庯臖昖臵躻噌䫘潊䔇懟婔臯鄘嚔脄䫘認婻誫啂镖劽䔇庘昄幽婫凹庯臖庘昄䔇䂷悩镖婺䔇懟婻噄䘹鄘嚔䫘潊婔婻膷庺臯㔗婉誺認婻媘脘噾䂟庘嚄庖婘儖準䔇䬽橸婺埇脘嚔赆役鍴㔗婋麵儌滇婔婻婘锬拷彖臘婺嘪䫘誫啂镖劽䔇庘昄䔇冋床

CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$
    SELECT name FROM nodes WHERE parent = $1
$$ LANGUAGE SQL;

SELECT * FROM nodes;
   name    | parent
-----------+--------
 Top       |
 Child1    | Top
 Child2    | Top
 Child3    | Top
 SubChild1 | Child1
 SubChild2 | Child1
(6 rows)

SELECT listchildren('Top');
 listchildren
--------------
 Child1
 Child2
 Child3
(3 rows)

SELECT name, listchildren(name) FROM nodes;
  name  | listchildren
--------+--------------
 Top    | Child1
 Top    | Child2
 Top    | Child3
 Child1 | SubChild1
 Child1 | SubChild2
(5 rows)

臙濘懟婘橔劯䔇 SELECT 麯澇橬庺䯄 Child2, Child3 京臯㔗認滇啹婺 listchildren 婺認底埗昄誫啂婔婻䷺镖劽啹溴婉䫘潊傂嘘䂷悩臯㔗

33.4.6. 崔攕䔇 SQL 庘昄

SQL 庘昄埇傖弄滯婺毖埖幽誫啂崔攕䔇 anyelement 启 anyarray 䌂傋㔗埗黙誗33.2.5诙埡橬噿崔攕庘昄䔇敘崔䂖誗㔗婋麵滇婔婻崔攕䔇庘昄 make_array 垄傯婴婻傂懟昄扞䌂傋噄䘹婺傺䆋婔婻昄䂇

CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
    SELECT ARRAY[$1, $2];
$$ LANGUAGE SQL;

SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
 intarray | textarray
----------+-----------
 {1,2}    | {a,b}
(1 row)

臙濘懟嘪䫘庖䌂傋蘸扵 'a'::text 弄滯埗昄滇 text 䌂傋㔗套悩埗昄埻滇婔婻庖严婾桺橸認滇媙釂䔇劥彍垄儌嚔赆嘷嘩 unknown 䌂傋㔗啹婺 unknown 婉滇婔䓉橬昽䔇䌂傋欔傖套悩澇橬䌂傋蘸扵儌嚔䩋彄䌂嚚婋麵認湙䔇髍臇媇敇

ERROR:  could not determine "anyarray"/"anyelement" type because input has type "unknown"

PostgreSQL 噕螩劆橬崔攕埗昄䔇庘昄誫啂婔婻啺垔䌂傋嘖滇埉誺準婉臯㔗懫套

CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
    SELECT $1 > $2;
$$ LANGUAGE SQL;

SELECT is_greater(1, 2);
 is_greater
------------
 f
(1 row)

CREATE FUNCTION invalid_func() RETURNS anyelement AS $$
    SELECT 1;
$$ LANGUAGE SQL;
ERROR:  cannot determine result data type
DETAIL:  A function returning "anyarray" or "anyelement" must have at least one argument of either type.

崔攕攓幘埇傖䫘庯闼底劆橬膷庺埗昄䔇庘昄㔗懫套

CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray)
AS 'select $1, array[$1,$1]' LANGUAGE sql;

SELECT * FROM dup(22);
 f2 |   f3
----+---------
 22 | {22,22}
(1 row)