【原創】PostgreSQL 遍曆表的PLSQL列舉

我們經常會對資料字典中的系統資料表進行遍曆，從而寫一些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 < cnt   loop       select id,log_time into strict o_out from tmp_1 where id > 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 mst_girl=#

本文出自 “上帝，咱們不見不散！” 部落格，請務必保留此出處http://yueliangdao0608.blog.51cto.com/397025/1351039