自定义函数 与 视图 在使用上有什么区别?

Question

测试环境: PostgreSQL
测试用例:

CREATE TABLE a (
    aid serial PRIMARY KEY,
    name varchar(20)
);    
CREATE TABLE b (
    bid serial PRIMARY KEY,
    name varchar(20)
);      
CREATE TABLE ab (
    abid serial PRIMARY KEY,
    aid serial REFERENCES a(aid),
    bid serial REFERENCES b(bid)
);     

CREATE VIEW v_ab
  AS
SELECT a.name AS "aname",
       b.name AS "bname",
       ab.abid AS "abid"
  FROM a, b, ab
 WHERE a.aid = ab.aid
   AND b.bid = ab.bid;

CREATE FUNCTION f_ab(int) 
    RETURNS TABLE(aname varchar, bname varchar) AS $$ 
        SELECT a.name AS "aname",
               b.name AS "bname"
          FROM a, b, ab
         WHERE a.aid = ab.aid
           AND b.bid = ab.bid
           AND ab.abid = $1 
$$ LANGUAGE SQL;

INSERT INTO a VALUES (1, 'a');
INSERT INTO b VALUES (2, 'b');
INSERT INTO ab VALUES (12, 1, 2);

这两篇文章把我搞糊涂了:慎用自定义函数和通过自定义函数提高服务器性能
抱着"存在即合理"的想法,我想知道到底什么时候该使用自定义函数?
另外,当数据量较大时,下面这两条语句执行效率上有区别吗?

SELECT aname, bname FROM v_ab WHERE abid = 12;
SELECT aname, bname FROM f_ab(12);

Answer 1

你的文章是SQL server的，PostgreSQL我倒没用过。

在SQL server中视图仅仅是SQL的映射，它一般事不含有程序运算，仅用于查询。 自定义函数是程序块，它支持参数，并且返回类型多样化。

数据库一般提供数据存储的作用，业务逻辑运算却由其它应用程序处理，例如JAVA。如果业务逻辑比较复杂而且高度依赖数据库里的各种数据，这样JAVA与数据库之间可能需要通讯多次，才可以把流程完成。这样无疑会产生交互的时间，所以如果把复杂的运算直接让数据库中计算出来，那效率就会相当高，所以就有了自定义函数。

Answer 2

回答自己提的第一个问题...
1. 什么时候该使用自定义函数?
* 如猫之良品所说
2. 什么时候该使用视图?
* 出于安全考虑,只希望用户查询表中的一部分数据
* 希望基于已存在的表,建立一张冗余表,以方便查询
* 由于使用视图进行select操作和普通表一致, 所以对视图进行查询比通过函数获得返回值更具有灵活性. 有复杂的查询逻辑, 但是又不伴随其它操作时, 可优先考虑使用视图, 否则考虑使用函数(如: has_topic).

对于2, 考虑如下场景:
1. topic和chat都只能回复topic
2. 加载主页面时, 将topic和chat无差别的按最后回复时间排序取出
一种处理办法是: 建立一张表message, 每当有chat或topic插入时, 通过触发器同时插入到message
但是这种办法浪费了大量的存储空间. 那么这种情况下, 通过视图建立一张虚拟表message_v无疑是更好的处理办法.(参见下面代码)

CREATE SEQUENCE message_seq;
CREATE TABLE topic (
    tid integer PRIMARY KEY DEFAULT nextval('message_seq'),
    content varchar(40000),
    reply_id integer REFERENCES topic(tid),
    submit_time timestamp,
    last_reply_time timestamp,
    title varchar(500)
);

CREATE TABLE chat (
    id integer PRIMARY KEY DEFAULT nextval('message_seq'),
    content varchar(40000),
    reply_id integer REFERENCES topic(tid),
    submit_time timestamp
);

CREATE VIEW message_v
  AS
SELECT *,
       submit_time AS "last_reply_time",
       NULL AS "title"
  FROM chat c
 UNION
SELECT *
  FROM topic t;

create or replace function
has_topic()
  returns bool
as $$
declare
    _res bool;
begin
    select true
      from topic
     where exists (select * from topic)
      into _res;
    if _res is null then
        _res := false;
    end if;
    return _res;
end;
$$ language plpgsql;

Answer 3

虽然已经采纳答案了，我还是来补充两句哈：

• 视图只读不写，从功能上讲，pgsql的视图只是自定义函数的一个子集
• 视图只是一个SQL语句，其占用的空间并不会随着记录行数增多而增大

• 和你手工JOIN相比，视图几乎没有性能提升，只有物化视图（Materialized View，Oracle和Postgresql都有）能大大提升查询性能

• 自定义函数和存储过程基本可以认为是同义词，在各个数据库产品中叫法不同而已。MYSQL叫用户定义函数（UDF），SQL Server叫存储过程（Stored Procedure），PGSQL里Procedural Language是UDF和Trigger的基础

• 自定义函数/存储过程有缓存查询计划的功能，当第二次查询SELECT * FROM tbl WHERE a = ?，只是改变了a=?的数值，那么就不需要再做SQL Parsing、Query Planing，直接用上次生成的查询计划。所以理论上讲，存储过程快于直接的SQL查询
• 又，因为缓存查询计划的缘故，存储过程对SQL注射攻击天然免疫。如上面这个语句，当用户通过HTTP传递一个这样的a值：123 or 1=1，它得不到SELECT * FROM tbl WHERE a = 123 or 1=1这样的查询计划，实现不了攻击效果
• 《慎用自定义函数》一文讲得对，存储过程中一些复杂的SQL会导致查询优化器作用减弱。你在存储过程里干这么简单的联合查询的活儿，不如直接用视图，如果你的查询有较复杂的if, else逻辑，写成存储过程还是值得的。