PostgreSQL Generating Alphanumeric ID from Integer

Question

I want to use this: https://stackoverflow.com/a/57907193/2253323

Or something similar to generate an alphanumeric string from a UNIX timestamp.

CREATE OR REPLACE FUNCTION consistent_shuffle(alphabet TEXT, salt TEXT) RETURNS TEXT AS $$
DECLARE
    SALT_LENGTH INT := length(salt);
    integer INT = 0;
    temp TEXT = '';
    j INT = 0;
    v INT := 0;
    p INT := 0;
    i INT := length(alphabet) - 1;
    output TEXT := alphabet;
BEGIN
    IF salt IS NULL OR length(LTRIM(RTRIM(salt))) = 0 THEN
        RETURN alphabet;
    END IF;
    WHILE i > 0 LOOP
        v := v % SALT_LENGTH;
        integer := ASCII(substr(salt, v + 1, 1));
        p := p + integer;
        j := (integer + v + p) % i;

        temp := substr(output, j + 1, 1);
        output := substr(output, 1, j) || substr(output, i + 1, 1) || substr(output, j + 2);
        output := substr(output, 1, i) || temp || substr(output, i + 2);

        i := i - 1;
        v := v + 1;
    END LOOP;
    RETURN output;
END;
$$ LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION generate_uid(id INT, min_length INT, salt TEXT) RETURNS TEXT AS $$
DECLARE
    clean_alphabet TEXT := 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890';
    curse_chars TEXT := 'csfhuit';
    curse TEXT := curse_chars || UPPER(curse_chars);
    alphabet TEXT := regexp_replace(clean_alphabet, '[' || curse  || ']', '', 'gi');
    shuffle_alphabet TEXT := consistent_shuffle(alphabet, salt);
    char_length INT := length(alphabet);
    output TEXT := '';
BEGIN
    WHILE id != 0 LOOP
        output := output || substr(shuffle_alphabet, (id % char_length) + 1, 1);
        id := trunc(id / char_length);
    END LOOP;
    curse := consistent_shuffle(curse, output || salt);
    output := RPAD(output, min_length, curse);
    RETURN output;
END;
$$ LANGUAGE plpgsql VOLATILE;

However I get the following error:

SELECT generate_uid(ceil(extract(epoch from now())*100000), 8, '20');

[42883] ERROR: function substr(text, bigint, integer) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Where: PL/pgSQL function demo_generate_uid(bigint,integer,text) line 12 at assignment

I tried changing id INT to BIGINT. But it doesn't fix the error.

Provide sample data, desired results, and an explanation of what you want to do. There might be a much simpler approach. — Gordon Linoff, Apr 27 '20 at 15:22
@GordonLinoff From an integer to generate an alphanumeric number similar to those of YouTube. — Nikk, Apr 27 '20 at 15:24
Hi! Have you tried this?: output := output || substr(shuffle_alphabet::text, (id % char_length) + 1, 1); — strobering, Apr 27 '20 at 15:37
@strobering `[22003] ERROR: integer out of range Where: PL/pgSQL function demo_generate_uid() line 13 during statement block local variable initialization`. I think it's because the unix timestamp is way longer than the `id INT` would allow. — Nikk, Apr 27 '20 at 15:41
Ok good, And why don't you replace with Bigint? I think that it should work now — strobering, Apr 27 '20 at 15:44
@strobering I did. Set `id BIGINT` and get the error in the original question. — Nikk, Apr 27 '20 at 15:46
Ok, but if you changed it: output := output || substr(shuffle_alphabet::text, (id % char_length) + 1, 1); shouldn't fail now — strobering, Apr 27 '20 at 15:49
It doesn't work. Same error as above. I am passing the following `generate_uid(158800291854464, 8, '20');` — Nikk, Apr 27 '20 at 15:56

score 0 · Accepted Answer · edited Apr 27 '20 at 16:15

0

1) Change int to BigInt

CREATE OR REPLACE FUNCTION public.generate_uid(
    id BigInt,
    min_length integer,
    salt text)
...

2) Change this line:

output := output || substr(shuffle_alphabet, (id % char_length) + 1, 1);

to:

 output := output || substr(shuffle_alphabet::text, ((id % char_length) + 1)::int, 1);

3) And then:

SELECT generate_uid(ceil(extract(epoch from now())*100000)::bigint, 8, '20');

return:

"wExwM7pR"

edited Apr 27 '20 at 16:15

answered Apr 27 '20 at 16:14

strobering

176
2
7

1

Perfect. Thank you. – Nikk Apr 27 '20 at 16:21

PostgreSQL Generating Alphanumeric ID from Integer

1 Answers1