pg的雪花函数

pg雪花算法，摘录网上，记录在此以备查。在此感谢原作者。

建一个序列：

CREATE SEQUENCE "public"."table_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 999999999999999
START 1
CACHE 1
CYCLE ;

SELECT setval('"public"."table_id_seq"', 17, true);

ALTER SEQUENCE "public"."table_id_seq" OWNER TO "postgres";

建立函数：

CREATE OR REPLACE FUNCTION "public"."fn_ snow_next_id"(OUT "result" int8)
  RETURNS "pg_catalog"."int8" AS $BODY$
DECLARE
   our_epoch bigint := 1314220021721;

   seq_id bigint;
   now_millis bigint;
   shard_id int := 5;
BEGIN
   seq_id := nextval('table_id_seq') % 1024;
   SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
   result := (now_millis - our_epoch) << 23;
   result := result | (shard_id << 10);
   result := result | (seq_id);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

ALTER FUNCTION "public"."fn_ snow_next_id"(OUT "result" int8) OWNER TO "postgres";

查看结果：

SELECT "public"."fn_ snow_next_id"(),"public"."fn_ snow_next_id"();

fn_ snow_next_id        fn_ snow_next_id(1)
2836366612074861584    2836366612074861585    

再执行一次：

fn_ snow_next_id(1)    fn_ snow_next_id
2836374421910852627    2836374421910852626