postgresql pg_xlog_location_diff 函式

pg_xlog_location_diff('','') 這個函式還是比較有用的，在安裝9.1時，沒有建立，需要手動建立，最好建立在 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=split_part($1,'/',2);
         xlog1=split_part($1,'/',1);
       offset2=split_part($2,'/',2);
         xlog2=split_part($2,'/',1);
        
       /* Prepare SQL query for calculation based on following formula
         (FF000000 * xlog + offset) - (FF000000 * xlog + offset)
         which gives value in hexadecimal. Since, hexadecimal calculation is cumbersome
         so convert into decimal and then calculate the difference */
        
       SQL='SELECT (x'''||'FF000000'||'''::bigint * x'''||xlog1||'''::bigint
                                +  x'''||offset1||'''::bigint)'||'
                -
                   (x'''||'FF000000'||'''::bigint * x'''||xlog2||'''::bigint
                                +  x'''||offset2||'''::bigint)';
       EXECUTE SQL into diff;
        
       /* Return the value in numeric by explicit casting  */
        
       RETURN diff::numeric;
    END;
 $function$;