我们经常会对数据字典中的系统表进行遍历，从而写一些shell脚本或者动态造数据等等。这里我用plsql演示了三种方法来遍历一张表。表结构如下，t_girl=#\\dtmp_1;u
我们经常会对数据字典中的系统表进行遍历，从而写一些shell脚本或者动态造数据等等。 这里我用plsql演示了三种方法来遍历一张表。
表结构如下，
t_girl=# \d tmp_1;unlogged table public.tmp_1 column |type| modifiers----------+-----------------------------+----------- id| integer| log_time | timestamp without time zone |
在这里我创建里一个自定义类型来保存我的函数返回值。
create type ytt_record as (id int,log_time timestamp without time zone);
现在来看第一个函数。 也是用最笨的方法来遍历。
create or replace function sp_test_record1(in f_id int) returns setof ytt_record as$ytt$declare i int;declare cnt int;declare o_out ytt_record;begin i := 0; cnt := 0; select count(*) into cnt from tmp_1 where id > f_id; while i f_id order by log_time desc limit 1 offset i;i := i + 1;return next o_out; end loop;end;$ytt$ language plpgsql;
我们来执行下结果，花费了3毫秒左右。
t_girl=# select * from sp_test_record1(60); id |log_time----+---------------------------- 85 | 2014-01-11 17:52:11.696354 73 | 2014-01-09 17:52:11.696354 77 | 2014-01-04 17:52:11.696354 80 | 2014-01-03 17:52:11.696354 76 | 2014-01-02 17:52:11.696354 65 | 2013-12-31 17:52:11.696354 80 | 2013-12-30 17:52:11.098336 85 | 2013-12-27 17:52:11.098336 97 | 2013-12-26 17:52:11.696354 94 | 2013-12-24 17:52:09.321394(10 rows)time: 3.338 ms
现在来看第二个函数，这个就比较优化了， 用了系统自带的循环遍历结构。
create or replace function sp_test_record2(in f_id int) returns setof ytt_record as$ytt$declare o_out ytt_record;begin for o_out in select id,log_time from tmp_1 where id > f_id order by log_time desc loopreturn next o_out; end loop;end;$ytt$ language plpgsql;
这次运行结果看看，时间不到1毫秒。
t_girl=# select * from sp_test_record2(60); id |log_time----+---------------------------- 85 | 2014-01-11 17:52:11.696354 73 | 2014-01-09 17:52:11.696354 77 | 2014-01-04 17:52:11.696354 80 | 2014-01-03 17:52:11.696354 76 | 2014-01-02 17:52:11.696354 65 | 2013-12-31 17:52:11.696354 80 | 2013-12-30 17:52:11.098336 85 | 2013-12-27 17:52:11.098336 97 | 2013-12-26 17:52:11.696354 94 | 2013-12-24 17:52:09.321394(10 rows)time: 0.660 ms
最后一个函数，， 利用return query 直接返回结果集。
create or replace function sp_test_record3(in f_id int) returns setof ytt_record as$ytt$begin return query select id,log_time from tmp_1 where id > f_id order by log_time desc ;end;$ytt$ language plpgsql;
这个结果其实等同于直接从表select，响应时间和第二个差不多。
t_girl=# select sp_test_record3(60);sp_test_record3----------------------------------- (85,2014-01-11 17:52:11.696354) (73,2014-01-09 17:52:11.696354) (77,2014-01-04 17:52:11.696354) (80,2014-01-03 17:52:11.696354) (76,2014-01-02 17:52:11.696354) (65,2013-12-31 17:52:11.696354) (80,2013-12-30 17:52:11.098336) (85,2013-12-27 17:52:11.098336) (97,2013-12-26 17:52:11.696354) (94,2013-12-24 17:52:09.321394)(10 rows)time: 0.877 mst_girl=#
本文出自 “上帝，咱们不见不散！” 博客，请务必保留此出处