sql - PostgreSQL 密码生成器

Question

我需要一些有关 sql pass 生成器的帮助。我已经有一个返回 8 个随机字符的函数，但我必须确定，有小写和大写字符和数字。有什么建议吗？这是我的旧函数。

CREATE FUNCTION f_generate_password() RETURNS text AS $$
DECLARE
   password text;
   chars text;
BEGIN
   password := '';
   chars := 
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
   FOR i IN 1..8 LOOP
     password := password || SUBSTRING(chars, 
ceil(random()*LENGTH(chars))::integer, 1);
   END LOOP;
   return password;
END;
$$
LANGUAGE plpgsql;

Answer 1

这是针对具有相同或相似问题的解决方案:)

CREATE OR REPLACE FUNCTION f_generate_password()
  RETURNS text AS
$BODY$
DECLARE
   vPassword text;
   chars text;
BEGIN
   vPassword := ''; 
   chars := 
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
  WHILE((select COALESCE(substring(vPassword from '.*[a-z]+.*'),'')) = '' OR (select COALESCE(substring(vPassword from '.*[A-Z]+.*'),'')) = '' OR (select COALESCE(substring(vPassword from '.*[0-9]+.*'),'')) = '') LOOP
  vPassword := '';
   FOR i IN 1..8 LOOP
     vPassword := vPassword || SUBSTRING(chars, ceil(random()*LENGTH(chars))::integer, 1);
  END LOOP;
   END LOOP;
   return vPassword;
END;
$BODY$
  LANGUAGE plpgsql;