Postgresql – How to TRIGGER/RETURNING this generator function in PostgreSQL

postgresqlprimary-keyrunique-constraint

I am providing a key table to my users in various categories so I am thinking how to TRIGGER/RETURNING the function with each SELECT.
Functions to generate shorthand unique IDs

  1. The thread answer Generating human-readable/usable, short but unique IDs but no salt so no.
  2. http://hashids.org/ can be a valid option as proposed by the answer here
  3. Any default PostgreSQL options?

Example of key table where I need the keys

Module 1 
1. <key1>
2. <key2>
3. ...

Module 2 
1. <key1>
2. <key2>

Keys are bound to the SERIAL primary keys in the table event_log.
I am thinking if the database can do the generation task for you i.e. how to bind your primary key to any algorithm that can generate such shorthand forms.
I have currently the data as the following in the table where SERIAL PRIMARY is not so clear

CREATE TABLE event_log (
    data_id SERIAL PRIMARY KEY NOT NULL,
    name VARCHAR(10) NOT NULL,
    module INTEGER NOT NULL, - - 1 or 2 or 3 as value
    time TIMESTAMP NOT NULL
);

Idea: Bind a TRIGGER and/or RETURNING nice_id to SELECT * FROM event_log WHERE module=1 queries

OS: Debian Linux 8.7
SQL: PostgreSQL 9.4 and/or R sqldf

Best Answer

I am thinking if the database can do the generation task for you i.e. how to bind your primary key to any algorithm that can generate such shorthand forms

Since you mention a salt, I assume you're looking for short strings that are hard to guess or reproduce by an outsider, despite being originated with a database sequence. The permuteseq extension essentially does that.

The function that produces a short string from an integer has to be provided, for instance:

CREATE FUNCTION id_to_alpha(n int) RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT AS $$
DECLARE
 alphabet text:='abcdefghijklmnopqrstuvwxyz012345678';
 sign_char char:='9';
 base int:=length(alphabet); 
 _n bigint:=abs(n);
 output text:='';
BEGIN
 LOOP
   output := output || substr(alphabet, 1+(_n%base)::int, 1);
   _n := _n / base;
   EXIT WHEN _n=0;
 END LOOP;
 RETURN CASE WHEN (n<0) THEN output || sign_char::text ELSE output END;
 RETURN output;
END; $$;

Then you may set the short id slug in a BEFORE INSERT trigger, like this:

CREATE EXTENSION permuteseq;

CREATE TABLE things( 
 id serial,
 short_id text,
 name varchar(10) not null
);

-- keep the sequence short for short output strings
ALTER SEQUENCE things_id_seq MAXVALUE 1000000;

CREATE FUNCTION generate_short_id() RETURNS TRIGGER AS $$
DECLARE
 secret bigint := 123456789;  -- change for a different secret sequence
BEGIN
 NEW.short_id := id_to_alpha(range_encrypt_element(NEW.id, 1, 10000000, secret)::int);
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER things_trigger BEFORE INSERT ON things 
FOR EACH ROW EXECUTE PROCEDURE generate_short_id();

Testing it:

INSERT INTO things(name) SELECT 'foo' FROM generate_series(1,10);
SELECT * FROM things;

 id | short_id | name 
----+----------+------
  1 | wf3hg    | foo
  2 | tm6lg    | foo
  3 | riqbg    | foo
  4 | p6jp     | foo
  5 | h3r3c    | foo
  6 | 3sx5d    | foo
  7 | w8ecd    | foo
  8 | km3le    | foo
  9 | llt1e    | foo
 10 | xwtxc    | foo

In this example, there is both id and short_id in the table, but id is technically redundant with short_id. You could create a sequence outside of the table without the SERIALsyntax like this, and not even have id in the table:

CREATE SEQUENCE seq_pk MAXVALUE 1000000;

CREATE TABLE things2(
 short_id TEXT 
    DEFAULT id_to_alpha(permute_nextval('seq_pk'::regclass, 123456::bigint)::int) 
    PRIMARY KEY,
 name text
);

INSERT INTO things2(name) values('foo');

SELECT * FROM things2;
 short_id | name 
----------+------
 znyh     | foo

If you can't use permuteseq, as an alternative still based on encrypting the ID, you may look at the plpgsql implementation of SKIP32, or XTEA for 64-bit (bigint) which won't require a compilation or being superuser. The drawback is that you cannot fine-tune the size of the output by changing the sequence's range.

Related Question