Skip to content

Instantly share code, notes, and snippets.

@kodekracker
Last active April 27, 2022 19:00
Show Gist options
  • Save kodekracker/3aca5bee52b8cfabaa75bddd2cbd4ce8 to your computer and use it in GitHub Desktop.
Save kodekracker/3aca5bee52b8cfabaa75bddd2cbd4ce8 to your computer and use it in GitHub Desktop.
A pseudo_encrypt function implementation for int and bigint
CREATE OR REPLACE FUNCTION pseudo_encrypt(value int) returns int AS $$
DECLARE
l1 int;
l2 int;
r1 int;
r2 int;
i int:=0;
BEGIN
l1:= (value >> 16) & 65535;
r1:= value & 65535;
WHILE i < 3 LOOP
l2 := r1;
r2 := l1 # ((((1366 * r1 + 150889) % 714025) / 714025.0) * 32767)::int;
l1 := l2;
r1 := r2;
i := i + 1;
END LOOP;
return ((r1 << 16) + l1);
END;
$$ LANGUAGE plpgsql strict immutable;
-- https://stackoverflow.com/a/12761795/2026325
CREATE OR REPLACE FUNCTION pseudo_encrypt(VALUE bigint) returns bigint AS $$
DECLARE
l1 bigint;
l2 bigint;
r1 bigint;
r2 bigint;
i int:=0;
BEGIN
l1:= (VALUE >> 32) & 4294967295::bigint;
r1:= VALUE & 4294967295;
WHILE i < 3 LOOP
l2 := r1;
r2 := l1 # ((((1366.0 * r1 + 150889) % 714025) / 714025.0) * 32767*32767)::int;
l1 := l2;
r1 := r2;
i := i + 1;
END LOOP;
RETURN ((l1::bigint << 32) + r1);
END;
$$ LANGUAGE plpgsql strict immutable;
@kodekracker
Copy link
Author

A random id generator

CREATE SEQUENCE global_id_sequence;
CREATE 
OR REPLACE FUNCTION id_generator(OUT result bigint) AS $$ 
DECLARE our_epoch bigint := 1314220021721;
seq_id bigint;
now_millis bigint;
-- the id of this DB shard, must be set for each     
-- schema shard you have - you could pass this as a parameter too     
shard_id int := 1;
BEGIN 
SELECT 
  nextval('global_id_sequence') % 1024 INTO seq_id;
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;
$$ LANGUAGE PLPGSQL;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment