· 4 years ago · Feb 23, 2021, 12:34 PM
1CREATE DATABASE redish;
2
3SELECT redish;
4
5-- by Nathan Fritz (andyet.com); turbo (github.com/turbo)
6CREATE EXTENSION IF NOT EXISTS "pgcrypto";
7
8-- can't query pg_type because type might exist in other schemas
9-- no IF NOT EXISTS for CREATE DOMAIN, need to catch exception
10DO $$ BEGIN
11 CREATE DOMAIN SHORTKEY as varchar(11);
12EXCEPTION
13 WHEN duplicate_object THEN null;
14END $$;
15
16CREATE OR REPLACE FUNCTION shortkey_generate()
17RETURNS TRIGGER AS $$
18DECLARE
19 gkey TEXT;
20 key SHORTKEY;
21 qry TEXT;
22 found TEXT;
23 user_id BOOLEAN;
24BEGIN
25 -- generate the first part of a query as a string with safely
26 -- escaped table name, using || to concat the parts
27 qry := 'SELECT id FROM ' || quote_ident(TG_TABLE_NAME) || ' WHERE id=';
28
29 LOOP
30 -- deal with user-supplied keys, they don't have to be valid base64
31 -- only the right length for the type
32 IF NEW.id IS NOT NULL THEN
33 key := NEW.id;
34 user_id := TRUE;
35
36 IF length(key) <> 11 THEN
37 RAISE 'User defined key value % has invalid length. Expected 11, got %.', key, length(key);
38 END IF;
39 ELSE
40 -- 8 bytes gives a collision p = .5 after 5.1 x 10^9 values
41 gkey := encode(gen_random_bytes(8), 'base64');
42 gkey := replace(gkey, '/', '_'); -- url safe replacement
43 gkey := replace(gkey, '+', '-'); -- url safe replacement
44 key := rtrim(gkey, '='); -- cut off padding
45 user_id := FALSE;
46 END IF;
47
48 -- Concat the generated key (safely quoted) with the generated query
49 -- and run it.
50 -- SELECT id FROM "test" WHERE id='blahblah' INTO found
51 -- Now "found" will be the duplicated id or NULL.
52 EXECUTE qry || quote_literal(key) INTO found;
53
54 -- Check to see if found is NULL.
55 -- If we checked to see if found = NULL it would always be FALSE
56 -- because (NULL = NULL) is always FALSE.
57 IF found IS NULL THEN
58 -- If we didn't find a collision then leave the LOOP.
59 EXIT;
60 END IF;
61
62 IF user_id THEN
63 -- User supplied ID but it violates the PK unique constraint
64 RAISE 'ID % already exists in table %', key, TG_TABLE_NAME;
65 END IF;
66
67 -- We haven't EXITed yet, so return to the top of the LOOP
68 -- and try again.
69 END LOOP;
70
71 -- NEW and OLD are available in TRIGGER PROCEDURES.
72 -- NEW is the mutated row that will actually be INSERTed.
73 -- We're replacing id, regardless of what it was before
74 -- with our key variable.
75 NEW.id = key;
76
77 -- The RECORD returned here is what will actually be INSERTed,
78 -- or what the next trigger will get if there is one.
79 RETURN NEW;
80END
81$$ language 'plpgsql';
82
83CREATE TABLE containers(
84 id SHORTKEY PRIMARY KEY,
85 size VARCHAR(24) NOT NULL,
86);
87
88CREATE TABLE IF NOT EXISTS qr_codes(
89 id SHORTKEY PRIMARY KEY
90 generated_at TIMESTAMP NOT NULL DEFAULT NOW(),
91 note VARCHAR(255),
92
93 CONSTRAINT fk_container
94 FOREIGN KEY(container_id)
95 REFERENCES containers(id)
96);
97