Function Scan

名称

函数扫描算子

介绍

函数扫描算子可以从函数的返回值中获取元组，扫描对象是返回元组集的函数。

举例

--清理环境
DROP FUNCTION fn1(int);
DROP TABLE t1 CASCADE;

--创建环境
CREATE TABLE t1(a int);
INSERT INTO t1 SELECT generate_series(1,10);
ANALYZE t1;

CREATE FUNCTION fn1(num int) RETURN SETOF record AS
DECLARE
    rec t1%rowtype;
BEGIN
    FOR rec in SELECT * FROM t1 WHERE a < num LOOP
        RETURN NEXT rec;
    END LOOP;
    RETURN;
END;
/

EXPLAIN ANALYZE SELECT * FROM fn1(5) AS (a int);
                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------
 Function Scan on FN1  (cost=0.00..12.50 rows=1000 width=4) (actual time=0.47..0.47 rows=4 loops=1)
 Planning Time: 0.04 msec
 Execution Time: 0.53 msec
(3 rows)

• on FN1 限定条件，扫描算子作用的函数
• Filter 限定条件，对函数的返回结果进行限定