PostgreSQL自定义函数处理特殊需求

发布时间：2020-12-13 16:59:53 所属栏目：百科来源：网络整理

导读：工作中，在某些特殊需求下，基本的SQL语句已经不能满足业务需求，需要使用函数来处理。 1. 需要新建sequence。由于部分表已经有数据，这时，新建的sequence的起始位置不能从1开始。 ? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

工作中，在某些特殊需求下，基本的SQL语句已经不能满足业务需求，需要使用函数来处理。

1. 需要新建sequence。由于部分表已经有数据，这时，新建的sequence的起始位置不能从1开始。

 
   ? 
  
        CREATE 
         OR 
         REPLACE 
         FUNCTION 
         create_sequence(sequence_name  
        character 
         varying 
        ,table_name  
        ) 
       
        --sequence_name 是需要新建的sequence名称 
       
        --table_name 是sequence作用的表 
       
        --column_name 是统计当前表的数据量的列值 
       
        RETURNS 
         bigint 
         AS 
       
        $BODY$ 
       
        DECLARE 
       
        startNum int8; 
       
        createsequence  
        "varchar" 
         :=  
        '' 
        ; 
       
        selectsequence  
        ; 
       
        BEGIN 
       
        --先获取当面表中已经保存了多少数据 
       
        selectsequence :=  
        'select COALESCE(max(' 
        ||column_name|| 
        '),0) + 1 FROM ' 
        || table_name || 
        ; 
       
        --将查询出来的结果放入startNum中 
       
        execute 
         selectsequence  
        into 
         startNum; 
       
        --声明 创建 sequence的语句。 
       
        createsequence :=  
        'CREATE SEQUENCE ' 
        ||sequence_name|| 
        ' 
       
        INCREMENT 1 
       
        MINVALUE 1 
       
        MAXVALUE 9223372036854775807 
       
        START ' 
        || startNum || 
        '  
       
        CACHE 1' 
        ; 
       
        --执行创建sequence的SQL语句 
       
         createsequence; 
       
        RETURN 
         startNum; 
       
        END 
        ; 
       
        $BODY$ 
       
        LANGUAGE plpgsql VOLATILE 
       
        COST 100; 
       
        ALTER 
         create_sequence( 
        ) OWNER  
        TO 
         postgres;

2. 需求，需要将这两行数据每列相加。

初始数据：

结果为<喎?"/kf/ware/vc/" target="_blank" class="keylink">vcD4KPHA+PGltZyBzcmM9"/uploadfile/Collfiles/20141219/2014121909224898.jpg" alt="">

2.1 先将将string转换为数组。如1,2,3,4,5转换为 [1,5]

tools_str2array(_originstr text)

 
         integer 
        []  
        AS 
       

 
        $BODY$ 
       

 
        declare 
         _cindex  
        INTEGER 
        ; 
       

 
         _arrIndex  
        ;

 
        DECLARE 
         _arr_str int4[];

 
         _tmp_str  
        VARCHAR 
        (10); 
       

 
         _delimeter  
        (1);

 
        BEGIN 
       

 
        _arrIndex:=1; 
       

 
        _cindex:=1; 
       

 
        _delimeter:= 
        ',' 
        ; 
       

 
        --_strres:=_strres||'原始字符串是：'||_originStr; 
       

 
        while _cindex< 
        "length" 
        (_originStr) loop 
       

 
        --_strres :=_strres||'【这个是什么？】'||split_part(_originStr,_delimeter,_arrIndex); 
       

 
        _tmp_str:=split_part(_originStr,_arrIndex); 
       

 
        if  
        "character_length" 
        (_tmp_str)<1  
        then 
       

 
        exit; 
       

 
        end 
         if;

 
        _arr_str:=_arr_str||  
        CAST 
        (_tmp_str  
        as 
         int4); 
       

 
        _arrIndex:=_arrIndex+1; 
       

 
         loop; 
       

 
        return 
         _arr_str;

;

 
        $BODY$ 
       

 
        LANGUAGE plpgsql VOLATILE 
       

 
        COST 100; 
       

 
         tools_str2array(text) OWNER  
         2.2. 新增聚集函数。
 
? 
  
        6 
        
     CREATE 
         AGGREGATE hot_map_test( 
        BASETYPE = TEXT, 
        --输入值 
       
        SFUNC = csk_test_start,0)!important; background:none!important">--最初执行的函数 
       
        STYPE = TEXT[],0)!important; background:none!important">--输出类型 
       
        FINALFUNC = count_hot_map 
        --最后执行的函数 
       
        );

 

  

  
2.3 新建计算函数 计算结果 
 
 
 
  ? 
   
  
        32 
      
        33 
      

        34 
      

        35 
      

        36 
      

        37 
      

        38 
      

        39 
      

        40 
      

        41 
      

        42 
      

        43 
      

        44 
      

        45 
      

        46 
      

        47 
      
 
       
       
       
       
       
       
       
       
       
       
       
       
       
       
        
      AGGREGATE count_test( 
        
        
         
         
         
        FINALFUNC = count_hot_map 
       
 
        
        ); 
        
       
          
        
        
         csk_test_start(a text[],s text) 
        
        
        RETURNS 
         text[]  
        AS 
        
        
        $BODY$ 
        
        
        BEGIN 
        
        
         a || s; 
        
        
        ; 
        
        
        $BODY$ 
        
        
        LANGUAGE plpgsql VOLATILE 
        
        
        COST 100; 
        
        
         csk_test_start(text[],text) OWNER  
         postgres; 
        
       
          
        
        
         count_hot_map(text[]) 
        
        
        AS 
        
        
        $BODY$ 
        
        
        DECLARE 
        
        
        startNum int8; 
        
        
        spilt_sql  
        ; 
        
        
        ; 
        
        
        temp_array int4[]; 
        
        
        result_array int4[]; 
        
       
          
        
        
        BEGIN 
        
        
        for 
         index 
         in 
         1..array_length($1,1) loop 
        
        
        spilt_sql =  
        'select tools_str2Array(' 
        ||$1[ 
        index 
        ]|| 
        '' 
        ')' 
        ; 
        
        
         spilt_sql  
         temp_array; 
        
        
         
        
        
        for 
         array_items_index  
        in 
         1..array_length(temp_array,1) loop 
        
        
        if result_array[array_items_index]  
        is 
         null 
         then 
        
        
        result_array[array_items_index] = 0; 
        
        
         if;  
        
        
         
        
        
        result_array[array_items_index] = result_array[array_items_index] + temp_array[array_items_index]; 
        
        
         loop; 
        
        
         loop; 
        
        
         result_array; 
        
        
        ; 
        
        
        $BODY$ 
        
        
        LANGUAGE plpgsql VOLATILE 
        
        
        COST 100; 
        
        
         count_hot_map(text[]) OWNER  
         postgres; 
        
        
     
    
   
  
                        （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!