在plpgsql中生成唯一的随机字符串

本文介绍了在plpgsql中生成唯一的随机字符串的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试编写一个函数来创建可变长度的唯一随机令牌.但是，我对plpgsql语法感到困惑.我的意图是创建一个功能

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

• 将表和列作为输入
• 生成具有给定字符集的给定长度的随机字符串
• 检查字符串是否已经在列中
• 如果是这样(预计这种情况很少见)，只需生成一个新的随机字符串.
• 否则，返回随机字符串

我当前的尝试如下:

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:"REPEAT"或附近的语法错误

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?

推荐答案

plpgsql中没有repeat语句.使用简单的 loop .

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;

请注意，random_string应该是format()的参数.

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

更新.根据Abelisto的准确提示，对于大桌子，这应该更快:

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; ...