postgresqlpg_xlog_location_diff函数

pg_xlog_location_diff(&＃39;&＃39;,&＃39;&＃39;) 这个函数还是比较有用的&＃xff0c;在安装9.1时&＃xff0c;没有创建&＃xff0c;需要手动创建&＃xff0c;最好创建在 postgres 数据库下的 public 下。

CREATE OR REPLACE FUNCTION public.pg_xlog_location_diff(text, text)

 RETURNS numeric
 LANGUAGE plpgsql
AS $function$
    DECLARE
       offset1 text;
       offset2 text;
       xlog1 text;
       xlog2 text;
       SQL   text;
       diff text;
    BEGIN
       /* Extract the Offset and xlog from input in
          offset and xlog variables */
           
       offset1&＃61;split_part($1,&＃39;/&＃39;,2);
         xlog1&＃61;split_part($1,&＃39;/&＃39;,1);
       offset2&＃61;split_part($2,&＃39;/&＃39;,2);
         xlog2&＃61;split_part($2,&＃39;/&＃39;,1);
        
       /* Prepare SQL query for calculation based on following formula
         (FF000000 * xlog &＃43; offset) - (FF000000 * xlog &＃43; offset)
         which gives value in hexadecimal. Since, hexadecimal calculation is cumbersome
         so convert into decimal and then calculate the difference */
        
       SQL&＃61;&＃39;SELECT (x&＃39;&＃39;&＃39;||&＃39;FF000000&＃39;||&＃39;&＃39;&＃39;::bigint * x&＃39;&＃39;&＃39;||xlog1||&＃39;&＃39;&＃39;::bigint
                                &＃43;  x&＃39;&＃39;&＃39;||offset1||&＃39;&＃39;&＃39;::bigint)&＃39;||&＃39;
                -
                   (x&＃39;&＃39;&＃39;||&＃39;FF000000&＃39;||&＃39;&＃39;&＃39;::bigint * x&＃39;&＃39;&＃39;||xlog2||&＃39;&＃39;&＃39;::bigint
                                &＃43;  x&＃39;&＃39;&＃39;||offset2||&＃39;&＃39;&＃39;::bigint)&＃39;;
       EXECUTE SQL into diff;
        
       /* Return the value in numeric by explicit casting  */
        
       RETURN diff::numeric;
    END;
 $function$;