频道导航

前端之家
Postgre SQL
PostgreSQL 遍历表的PLSQL列举
PostgreSQL 遍历表的PLSQL列举

                         2020-06-17
                         Postgre SQL
                         前端之家
                    
前端之家收集整理的这篇文章主要介绍了PostgreSQL 遍历表的PLSQL列举，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
 我们经常会对数据字典中的系统表进行遍历，从而写一些SHELL脚本或者动态造数据等等。 这里我用PLsql演示了三种方法来遍历一张表。 
 
 表结构如下，
 
        t_girl=# \d tmp_1; 
       
        Unlogged 
        table 
        @H_404_39@"public.tmp_1" 
       
        Column 
        | Type | Modifiers 
       
        ----------+-----------------------------+----------- 
       
        id | 
        integer 
        | 
       
        log_time | 
        timestamp 
        without 
        time 
        zone | 
       
 在这里我创建里一个自定义类型来保存我的函数返回值。

        1 
      
        create 
        type ytt_record 
        as 
        (id 
        int 
        ,log_time 
        zone); 
       
 现在来看第一个函数。 也是用最笨的方法来遍历。

        6 
      
        7 
      
        8 
      
        9 
      
        10 
      
        11 
      
        12 
      
        13 
      
        14 
      
        15 
      
        16 
      
        17 
      
        18 
      
        19 
      
     create 
        orreplacefunctionsp_test_record1(
 
        IN 
        f_id 
        int 
       
        ) 
        returns 
        setof ytt_record 
        as 
       
        $ytt$ 
       
        declare 
        i 
        ; 
       
        cnt 
        ; 
       
        o_out ytt_record; 
       
        begin 
       
        i := 0; 
       
        cnt := 0; 
       
        select 
        count 
        (*) 
        into 
        from 
        tmp_1 
        where 
        id > f_id; 
       
        while i < cnt 
       
        loop 
       
        select 
        id,monospace!important; font-size:1em!important; min-height:inherit!important">strict o_out 
        id > f_id 
        order 
        by 
        log_time 
        desc 
        limit 1 offset i; 
       
        i := i + 1; 
       
        return 
        next 
        o_out; 
       
        end 
        loop; 
       
        ; 
       
        $ytt$ language plpgsql; 
       
 我们来执行下结果，花费了3毫秒左右。

        15 
      
     t_girl=# 
        *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

现在来看第二个函数，这个就比较优化了，用了系统自带的循环遍历结构。

sp_test_record2(

o_out ytt_record;

begin

for o_out in desc

loop

o_out;

loop;

;

 这次运行结果看看，时间不到1毫秒。

sp_test_record2(60);

: 0.660 ms

最后一个函数，利用RETURN QUERY 直接返回结果集。

sp_test_record3(

begin

return query ;

 这个结果其实等同于直接从表SELECT，响应时间和第二个差不多。

        16 
      
     sp_test_record3(60); 
       
        sp_test_record3 
       
        ----------------------------------- 
       
        (85, 
        @H_404_39@"2014-01-11 17:52:11.696354" 
        ) 
       
        (73,monospace!important; font-size:1em!important; min-height:inherit!important; color:blue!important">"2014-01-09 17:52:11.696354" 
        ) 
       
        (77,monospace!important; font-size:1em!important; min-height:inherit!important; color:blue!important">"2014-01-04 17:52:11.696354" 
        ) 
       
        (80,monospace!important; font-size:1em!important; min-height:inherit!important; color:blue!important">"2014-01-03 17:52:11.696354" 
        ) 
       
        (76,monospace!important; font-size:1em!important; min-height:inherit!important; color:blue!important">"2014-01-02 17:52:11.696354" 
        ) 
       
        (65,monospace!important; font-size:1em!important; min-height:inherit!important; color:blue!important">"2013-12-31 17:52:11.696354" 
        ) 
       
        "2013-12-30 17:52:11.098336" 
        ) 
       
        "2013-12-27 17:52:11.098336" 
        ) 
       
        (97,monospace!important; font-size:1em!important; min-height:inherit!important; color:blue!important">"2013-12-26 17:52:11.696354" 
        ) 
       
        (94,monospace!important; font-size:1em!important; min-height:inherit!important; color:blue!important">"2013-12-24 17:52:09.321394" 
        ) 
       
        : 0.877 ms 
       
        t_girl=#