PostgreSQL学习(6) - SQL函数

第六章：SQL函数

参考：《PostgreSQL 9.6.0 手册》

1. 一个创建函数的例子

• 任何命令集合都能被打包在一起并且被定义成一个函数，如INSERT、SELECT、UPDATE、ALTER
• 函数体不能包含事务控制命令，如COMMIT、SAVEPOINT
• 除了定义void返回值的函数以外，最后的命令必须是一个SELECT或带有一个RETURNING子句，必须返回符合函数返回类型

--无参数，返回void
CREATE FUNCTION delete_order() RETURNS void AS $$        --记得加括号
    DELETE FROM customer_order
        WHERE product_price < 21; $$ LANGUAGE SQL;       --注意有两个封号

SELECT delete_order();                                   --用select执行函数

2. SQL函数的参数

• 函数体中参数引用有两种方式：1. 用参数名称argument_name    2. 编号引用：$1，$2.....（$1表示第一个参数）
• 如果参数名称与表的列名相同，则被覆盖，但可才采用: function_name.argument_name的形式
• 如果该参数是组合类型，则可以用argument_name.fieldname或者$1.fieldname的形式访问参数的域/属性; 同样的，如果与表列名相同，采用function_name.argument_name.fieldname的形式。

3. 基本类型上的SQL函数

CREATE FUNCTION muti(x integer,y integer) RETURNS integer AS $$     --注意RETURNS有个‘s’，别漏了
    SELECT x*y; $$ LANGUAGE SQL;

SELECT muti(4,5);

再一个例子：设置某个订单产品数量，并返回该订单总价 。

CREATE FUNCTION edit_order(order_id integer,order_num integer) RETURNS integer AS $$
    UPDATE customer_order
        SET order_num=edit_order.order_num      --order_num参数与表customer_order重复
        WHERE order_id=edit_order.order_id;     --每条SQL语句后面都要记得加封号，‘；’
    SELECT order_id*order_num; $$ LANGUAGE SQL; --本来想order_id*order_price
                                                --据说还可以用RETURNING返回，不知道啥区别
SELECT edit_order(4,1000);

4. 组合类型上的SQL函数

（1）组合类型作为参数

CREATE FUNCTION get_price(product) RETURNS integer AS $$   --此时的product标识product组合类型，而不是product表
    SELECT $1.product_price::integer; $$ LANGUAGE SQL;
    
SELECT product_name,get_price(product.*) FROM product;     --注意product.*的用法

（2）组合类型作为返回值（返回一个表中，满足条件的行）

CREATE FUNCTION get_a_line(product_id integer) RETURNS product AS $$
      SELECT product_id, product_price, product_name FROM product
            WHERE product_id = get_a_line.product_id; $$ LANGUAGE SQL;

SELECT get_a_line(2);

5. 带有输出参数的SQL函数

CREATE FUNCTION add_stdout(IN x integer,IN y integer,OUT sum integer, OUT muti integer)
    AS $$ SELECT x+y,x*y; $$ LANGUAGE SQL;

select product_name, add_stdout(product_price::integer,product_id) FROM product;

6. 带有可变数量参数的SQL函数

--跳过～

7. 带有默认值参数的SQL函数

CREATE FUNCTION discount_for_vip(product_price real,order_num integer,discount numeric default 0.8) 
    RETURNS numeric AS $$
        SELECT (product_price*order_num*discount_for_vip.discount)::numeric FROM customer_order; $$ LANGUAGE SQL;

SELECT order_id , discount_for_vip(product_price,order_num,0.9) FROM customer_order;
SELECT order_id , discount_for_vip(product_price,order_num) FROM customer_order;    --使用默认的折扣率

 

8. SQL函数作为表来源

所有的 SQL 函数都可以被用在查询的FROM子句中，其中，返回组合类型的函数特别有用，本部分跳过～

9. 返回TABLE的SQL函数

本部分跳过～