SQL語言函數 · postgresql手冊

### [PostgreSQL學習手冊(SQL語言函數)](http://www.cnblogs.com/stephen-liu74/archive/2012/06/04/2312194.html) Posted on?2012-06-04 09:04?[Stephen_Liu](http://www.cnblogs.com/stephen-liu74/)?閱讀(1577) 評論(3)?[編輯](http://www.cnblogs.com/stephen-liu74/admin/EditPosts.aspx?postid=2312194)?[收藏](http://www.cnblogs.com/stephen-liu74/archive/2012/06/04/2312194.html#)?![](https://box.kancloud.cn/2015-10-30_5632e1b8ae60b.jpg) **一、基本概念：** ?? ?SQL函數可以包含任意數量的查詢，但是函數只返回最后一個查詢(必須是SELECT)的結果。在簡單情況下，返回最后一條查詢結果的第一行。如果最后一個查詢不返回任何行，那么該函數將返回NULL值。如果需要該函數返回最后一條SELECT語句的所有行，可以將函數的返回值定義為集合，即SETOF sometype。 ?? ?SQL函數的函數體應該是用分號分隔的SQL語句列表，其中最后一條語句之后的分號是可選的。除非函數聲明為返回void，否則最后一條語句必須是SELECT。事實上，在SQL函數中，不僅可以包含SELECT查詢語句，也可以包含INSERT、UPDATE和DELETE等其他標準的SQL語句，但是和事物相關的語句不能包含其中，如BEGIN、COMMIT、ROLLBACK和SAVEPOINT等。 ?? ?CREATE FUNCTION命令的語法要求函數體寫成一個字符串文本。通常來說，該文本字符串常量使用美元符($$)圍住，如： **?? ?CREATE FUNCTION**?clean_emp()?**RETURNS void AS $$** ?? ???? DELETE FROM emp WHERE salary < 0; **?? ?$$ LANGUAGE SQL**; ?? ?最后需要說明的是SQL函數中的參數，PostgreSQL定義$1表示第一個參數，$2為第二個參數并以此類推。如果參數是復合類型，則可以使用點表示法，即$1.name訪問復合類型參數中的name字段。需要注意的是函數參數只能用作數據值，而不能用于標識符，如： ?? ?INSERT INTO mytable VALUES (**$1**);???*--合法* ?? ?INSERT INTO?**$1**?VALUES (42);??????????*--不合法(表名屬于標示符之一)* ???? **二、基本類型：** ?? ? ?? ?最簡單的SQL函數可能就是沒有參數且返回基本類型的函數了，如： ?? ?CREATE FUNCTION one() RETURNS integer AS $$ ?? ???? SELECT 1 AS result; ?? ?$$ LANGUAGE SQL; ??? 下面的例子聲明了基本類型作為函數的參數。 ?? ?CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$ ?? ???? SELECT $1 + $2; ?? ?$$ LANGUAGE SQL; ?? ?*# 通過select調用函數。* ?? ?*postgres=# SELECT add_em(1,2) AS answer;* ?? ? answer ?? ?-------- ?? ?????? 3 ?? ?(1 row)??? ???? ?? ?在下面的例子中，函數體內包含多個SQL語句，它們之間是用分號進行分隔的。 ?? ?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; ?? ? **三、復合類型****：** ?? ?見如下示例： ?? ?1). 創建數據表，這樣與之對應的復合類型也隨之生成。 ?? ?CREATE TABLE emp ( ?? ???? name??????? text, ?? ???? salary????? numeric, ?? ???? age???????? integer, ?? ?); ?? ?2). 創建函數，其參數為復合類型。在函數體內，可以像引用基本類型參數那樣引用復合類型，如$1。訪問復合類型的字段使用點表達式即可，如：$1.salary。 ?? ?CREATE FUNCTION double_salary(emp) RETURNS integer AS $$ ?? ???? SELECT ($1.salary * 2)::integer AS salary; ?? ?$$ LANGUAGE SQL; ?? ?3). 在select語句中，可以使用emp.*表示emp表的一整行數據。 ?? ?SELECT name, double_salary(emp.*) AS dream FROM emp WHERE age > 30; ?? ?4). 我們也可以使用ROW表達式構造自定義的復合類型，如： ?? ?SELECT name, double_salary(ROW(name, salary*1.1, age)) AS dream FROM emp; ?? ?5). 創建一個函數，其返回值為復合類型，如： ?? ?CREATE FUNCTION new_emp() RETURNS emp AS $$ ?? ???? SELECT ROW('None', 1000.0, 25)::emp; ?? ?$$ LANGUAGE SQL; ?? ?6). 調用返回復合類型的函數。 ??? SELECT new_emp(); ??? 7). 調用返回復合類型的函數，同時訪問該返回值的某個字段。 ??? SELECT (new_emp()).name; **四、帶輸出參數的函數：** ?? ?還有一種方法可以用于返回函數執行的結果，即輸出參數，如： **?? ?CREATE FUNCTION**?add_em2 (**IN**?x int,?**IN**?y int,?**OUT**?sum int)?**AS $$** ?? ??? ?SELECT $1 + $2 **?? ?$$ LANGUAGE SQL;** ??? 調用方法和返回結果與add_em(帶有返回值的函數)完全一致，如： ?? ?SELECT add_em(3,7); ?? ? ?? ?這個帶有輸出參數的函數和之前的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) ?? ?在上面的示例中，IN用于表示該函數參數為輸入參數(缺省值，可以忽略)，OUT則表示該參數為輸出參數。 **五、返回結果作為表數據源：** ?? ?所有SQL函數都可以在查詢的FROM子句里使用。該方法對于返回復合類型的函數而言特別有用，如果該函數定義為返回一個基本類型，那么該函數生成一個單字段表，如果該函數定義為返回一個復合類型，那么該函數生成一個復合類型里每個屬性組成的行。見如下示例： ??? 1). 創建一個數據表。 ?? ?CREATE TABLE foo ( ?? ??? ?fooid??? int,? ?? ??? ?foosubid int,? ?? ??? ?fooname? text ?? ?); ??? 2). 創建SQL函數，其返回值為與foo表對應的復合類型。 ?? ?CREATE FUNCTION getfoo(int) RETURNS foo AS $$ ?? ???? SELECT * FROM foo WHERE fooid = $1; ?? ?$$ LANGUAGE SQL; ??? 3). 在FROM子句中調用該函數。 ?? ?SELECT *, upper(fooname) FROM getfoo(1) AS t1; ?? ? **六、返回集合的SQL函數：** ?? ?如果SQL函數的返回值為SETOF sometype，那么在調用該函數時，將返回最后一個SELECT查詢的全部數據。這個特性通常用于把函數放在FROM子句里調用，見如下示例： ?? ?CREATE FUNCTION getfoo(int) RETURNS setof foo AS $$ ?? ???? SELECT * FROM foo WHERE fooid = $1; ?? ?$$ LANGUAGE SQL;?? ? ?? ?在FROM子句中調用了返回復合類型集合的函數，其結果等同于：*SELECT * FROM (SELECT * FROM foo WHERE fooid = 1) t1;* ?? ?SELECT * FROM getfoo(1) AS t1;?? ? ?? ? **七、多態的SQL函數：** ?? ?SQL函數可以聲明為接受多態類型(**anyelement**和**anyarray**)的參數或返回多態類型的返回值，見如下示例： ?? ?1). 函數參數和返回值均為多態類型。 ?? ?CREATE FUNCTION make_array(**anyelement, anyelement**) RETURNS?**anyarray**?AS $$ ?? ???? SELECT ARRAY[$1, $2]; ?? ?$$ LANGUAGE SQL; ?? ?其調用方式和調用其它類型的SQL函數完全相同，只是在傳遞字符串類型的參數時，需要顯式轉換到目標類型，否則將會被視為unknown類型，如： ?? ?SELECT make_array(1, 2) AS intarray, make_array(**'a'::text**, 'b') AS textarray; ?? ?2). 函數的參數為多態類型，而返回值則為基本類型。 ?? ?CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$ ?? ???? SELECT $1 > $2; ?? ?$$ LANGUAGE SQL; ?? ?3). 多態類型用于函數的輸出參數。 ?? ?CREATE FUNCTION dup (f1 anyelement,?**OUT**?f2 anyelement,?**OUT**?f3 anyarray) AS $$ ?? ??? ?SELECT $1, ARRAY[$1,$1] ?? ?$$ LANGUAGE sql; **八、函數重載：** ?? ?多個函數可以定義成相同的函數名，但是它們的參數一定要有所區分。換句話說，函數名可以重載，此規則有些類似于面向對象語言中的函數重載，見如下示例： ?? ?CREATE FUNCTION test(int, real) RETURNS ... ?? ?CREATE FUNCTION test(smallint, double) RETURNS ... ?? ?由于在PostgreSQL中函數支持重載，因此在刪除函數時，也必須指定參數列表，如： ?? ?DROP FUNCTION test(int, real); ?? ?DROP FUNCTION test(smallint,double);? 分類:?[PostgreSQL](http://www.cnblogs.com/stephen-liu74/category/343171.html)