Generate unique random strings in plpgsql

Question

I am trying to write a function to create unique random tokens of variable length. However, I am stumped by the plpgsql syntax. My intention is to create a function which

Takes a table and column as input
Generates a random string of a given length, with a given set of characters
Checks if the string is already in the colum
If so (and this is expected to be rare), simply generate a new random string.
Otherwise, return the random string

My current attempt looks like this:

CREATE FUNCTION random_token(_table TEXT, _column TEXT, _length INTEGER) RETURNS text AS $$
DECLARE
  alphanum CONSTANT text := 'abcdefghijkmnopqrstuvwxyz23456789';
  range_head CONSTANT integer := 25;
  range_tail CONSTANT integer := 33;
  random_string text;
BEGIN
  REPEAT
    SELECT substring(alphanum from trunc(random() * range_head + 1)::integer for 1) ||
      array_to_string(array_agg(substring(alphanum from trunc(random() * range_tail + 1)::integer for 1)), '')
      INTO random_string FROM generate_series(1, _length - 1);
  UNTIL random_string NOT IN FORMAT('SELECT %I FROM %I WHERE %I = random_string;', _column, _table, _column)
  END REPEAT;
  RETURN random_string;
END
$$ LANGUAGE plpgsql;

However, this doesn't work, and gives me a not very helpful error:

DatabaseError: error 'ERROR: syntax error at or near "REPEAT"

I have tried a number of variations, but without knowing what the error in the syntax is I am stumped. Any idea how to fix this function?

Do you know what a UUID is, why not just use it? You'll never (just about) generate a collision, and it stores in a more compact format and has default operators. — Evan Carroll
– Evan Carroll, Commented Feb 1, 2017 at 7:40

klin · Accepted Answer · 2016-10-12 20:19:01Z

4

There is no repeat statement in plpgsql. Use simple loop.

CREATE OR REPLACE FUNCTION random_token(_table TEXT, _column TEXT, _length INTEGER) RETURNS text AS $$
DECLARE
  alphanum CONSTANT text := 'abcdefghijkmnopqrstuvwxyz23456789';
  range_head CONSTANT integer := 25;
  range_tail CONSTANT integer := 33;
  random_string text;
  ct int;
BEGIN
  LOOP
    SELECT substring(alphanum from trunc(random() * range_head + 1)::integer for 1) ||
      array_to_string(array_agg(substring(alphanum from trunc(random() * range_tail + 1)::integer for 1)), '')
      INTO random_string FROM generate_series(1, _length - 1);
    EXECUTE FORMAT('SELECT count(*) FROM %I WHERE %I = %L', _table, _column, random_string) INTO ct;
    EXIT WHEN ct = 0;
  END LOOP;
  RETURN random_string;
END
$$ LANGUAGE plpgsql;

Note, random_string should be a parameter to format().

Update. According to the accurate hint from Abelisto, this should be faster for a large table:

DECLARE
  dup boolean;
...
    EXECUTE FORMAT('SELECT EXISTS(SELECT 1 FROM %I WHERE %I = %L)', _table, _column, random_string) INTO dup;
    EXIT WHEN NOT dup;
...

edited Oct 12, 2016 at 20:19

answered Oct 12, 2016 at 19:34

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Abelisto Over a year ago

BTW select count(*) ... is not the best way to check the existence of the value in the table...

pehrs Over a year ago

Thank you very much, I have spent half the day trying to get this to work.

Abelisto Over a year ago

@klin Simplest for developer but not for the server :o)

Abelisto Over a year ago

@klin I already upvoted for the your answer so I can not do it twice ;o)

Community · Accepted Answer · 2017-05-23 12:09:08Z

0

This is almost certainly not what you want. When you say, "checks if the string is already in the column" you're not referring to something that looks unique, you're referring to something that actually is UNIQUE.

Instead, I would point you over this answer I gave about UUIDs.

edited May 23, 2017 at 12:09

CommunityBot

11 silver badge

answered Feb 1, 2017 at 8:06

Evan Carroll

1

Collectives™ on Stack Overflow

Generate unique random strings in plpgsql

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related