【原创】PostgreSQL 遍历表的PLSQL列举

pgup12

我们经常会对数据字典中的系统表进行遍历，从而写一些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
  
•   loop
  
•      return 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 ms
  
• t_girl=#