How to TRIGGER/RETURNING this generator function in PostgreSQL?

Question

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

The thread answer Generating human-readable/usable, short but unique IDs but no salt so no.
http://hashids.org/ can be a valid option as proposed by the answer here
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

score 3 · Answer 1 · answered May 28 '17 at 12:17

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.

score 2 · Answer 2 · answered May 28 '17 at 09:23

You can just use a sequence, base64 codify the result, and use it as your key.

The concept is, given an awful_id, turn it into something a bit easier for humans (although we might not agree on whether 310276431 is easier or more difficult than MzEwMjc2NDMx):

 SELECT
     awful_id, 
     replace(encode(decode(awful_id::text || 'x', 'escape'), 'base64'), '=', '') AS "nicer_id??"
 FROM 
     generate_series(1, 1000, 13) as s(awful_id) ;

This would generate ...

 awful_id | nicer_id??
 -------: | :---------
        1 | MXg       
       14 | MTR4      
       27 | Mjd4      
       40 | NDB4      
[...]
      924 | OTI0eA    
      937 | OTM3eA    
      950 | OTUweA    
      963 | OTYzeA    
      976 | OTc2eA    
      989 | OTg5eA

You would then define your tables without a serial primary key, but based on a serial number:

You create a sequence for every table that needs a synthetic primary key:

CREATE SEQUENCE t_sq ;

You define the PK of your table based on this sequence, but encoded:

CREATE TABLE t
(
    nice_id char(10) 
        PRIMARY KEY 
        DEFAULT replace(encode(decode(nextval('t_sq') || 'x', 'escape'), 'base64'), '=', ''),
    more_data text
) ;

You would insert data into it as if your nice_id were a serial:

 INSERT INTO 
     t (more_data)
 VALUES
     ('a'),
     ('b'),
     ('c'),
     ('d')
 RETURNING
     t.* ;

 nice_id    | more_data
 :--------- | :--------
 MXg        | a        
 Mng        | b        
 M3g        | c        
 NHg        | d

dbfiddle here

How to TRIGGER/RETURNING this generator function in PostgreSQL?

2 Answers2