ANBOB™

在数据库中，有时需要一种快速简便的方法来生成随机字符串来填充文本列，因为目前PostgreSQL中没有生成随机字符串的函数，可以考虑使用 Orafce 扩展，其中包括一个生成随机字符串的PostgreSQL 函数。或者创建自定义函数实现，如果使用orafce中的dbms_random生成函数记的检查可能与oracle一些形为不同。

Oracle随机函数

SQL> desc dbms_random
PROCEDURE INITIALIZE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 VAL                            BINARY_INTEGER          IN
FUNCTION NORMAL RETURNS NUMBER
FUNCTION RANDOM RETURNS BINARY_INTEGER
PROCEDURE RECORD_RANDOM_NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 VAL                            NUMBER                  IN
FUNCTION REPLAY_RANDOM_NUMBER RETURNS NUMBER
PROCEDURE SEED
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 VAL                            BINARY_INTEGER          IN
PROCEDURE SEED
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 VAL                            VARCHAR2                IN
FUNCTION STRING RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OPT                            CHAR                    IN
 LEN                            NUMBER                  IN
PROCEDURE TERMINATE
FUNCTION VALUE RETURNS NUMBER
FUNCTION VALUE RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOW                            NUMBER                  IN
 HIGH                           NUMBER                  IN

SQL>  select substr(v,rownum,1) op,dbms_random.string( substr(v,rownum,1),10) vs
  2* from (select 'PXAUL' v from dual) connect by rownum<=length(v) ;
OP     VS
------ ------------------------------
P      JGm@k/Nep'
X      7W2G4JYSLZ
A      XWyGDbaRRE
U      VRRWEOJBPW
L      iqetujzydo

PostgreSQL随机函数
在PostgreSQL中有random生成随机数值的函数，但没有提供专门生成随机字符串的函数，然后也有人用写自定义函数，还有orafce插件带的oracle兼容dbms_random包。

[local]:5432 postgres@postgres=# \df random
                          List of functions
   Schema   |  Name  | Result data type | Argument data types | Type
------------+--------+------------------+---------------------+------
 pg_catalog | random | double precision |                     | func
(1 row)

[local]:5432 postgres@postgres=# select random();
      random
-------------------
 0.593365737465799
(1 row) 

[local]:5432 postgres@postgres=# CREATE OR REPLACE FUNCTION random_string(
postgres(# num INTEGER,
postgres(# chars TEXT default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
postgres(# ) RETURNS TEXT
postgres-# LANGUAGE plpgsql
postgres-# AS $$
postgres$# DECLARE
postgres$# res_str TEXT := '';
postgres$# BEGIN
postgres$# IF num < 1 THEN
postgres$# RAISE EXCEPTION 'Invalid length';
postgres$# END IF;
postgres$# FOR __ IN 1..num LOOP
postgres$# res_str := res_str || substr(chars, floor(random() * length(chars))::int + 1, 1);
postgres$# END LOOP;
postgres$# RETURN res_str;
postgres$# END $$;
CREATE FUNCTION

[local]:5432 postgres@postgres=# SELECT random_string(10);
random_string
---------------
qomJVcj4Lm
(1 row)

[local]:5432 postgres@postgres=# SELECT random_string(10,'abcd');
random_string
---------------
bcbbbacddc
(1 row)
 
-- orafce

[local]:5432 postgres@postgres=# \df dbms_random.value
                                      List of functions
   Schema    | Name  | Result data type |             Argument data types             | Type
-------------+-------+------------------+---------------------------------------------+------
 dbms_random | value | double precision |                                             | func
 dbms_random | value | double precision | low double precision, high double precision | func
(2 rows)

[local]:5432 postgres@postgres=# \df dbms_random.string
                           List of functions
   Schema    |  Name  | Result data type |  Argument data types  | Type
-------------+--------+------------------+-----------------------+------
 dbms_random | string | text             | opt text, len integer | func
(1 row)


[local]:5432 postgres@postgres=# select opt,DBMS_RANDOM.STRING(opt,30) from regexp_split_to_table('PXAUL','') AS OPT;
 opt |             string
-----+--------------------------------
 P   | -TYcMX99`-'u[!:qVZdJ:J2L>@gI:$
 X   | 1Z6QE4MH8MMMTV4O01KBUE4V93DKS8
 A   | fSloEFwkfIQLoHFurGwUWoqdiDjChZ
 U   | UGVANLQYQTQLMXCBHLSTIQAQSEFWSI
 L   | wnosncddbtxrfjohlwsdpatqrmuwjn
(5 rows)

[local]:5432 postgres@postgres=# select DBMS_RANDOM.STRING('l',30) FROM GENERATE_SERIES(1,10);
             string
--------------------------------
 ffbuxowbsyuppzzehkaanqahhstcoc
 ffbuxowbsyuppzzehkaanqahhstcoc
 ffbuxowbsyuppzzehkaanqahhstcoc
 ffbuxowbsyuppzzehkaanqahhstcoc
 ffbuxowbsyuppzzehkaanqahhstcoc
 ffbuxowbsyuppzzehkaanqahhstcoc
 ffbuxowbsyuppzzehkaanqahhstcoc
 ffbuxowbsyuppzzehkaanqahhstcoc
 ffbuxowbsyuppzzehkaanqahhstcoc
 ffbuxowbsyuppzzehkaanqahhstcoc
(10 rows)

[local]:5432 postgres@postgres=# \df+ DBMS_RANDOM.STRING
                                                                                        List of functions
   Schema    |  Name  | Result data type |  Argument data types  | Type | Volatility | Parallel |  Owner   | Security | Access privileges | Language |    Source code     |      Descript
ion
-------------+--------+------------------+-----------------------+------+------------+----------+----------+----------+-------------------+----------+--------------------+--------------
---------
 dbms_random | string | text             | opt text, len integer | func | immutable  | unsafe   | postgres | invoker  |                   | c        | dbms_random_string | Create Random
 Strings
(1 row)

Note:
默认dbms_random为immutable一个语句中对函数的多次调用，其中函数执行一次，结果被缓存为下一次调用，所以是相同的值，这点与oracle不同，但比较容易解决，改为volatile。

[local]:5432 postgres@postgres=# alter function DBMS_RANDOM.STRING volatile;
ALTER FUNCTION
[local]:5432 postgres@postgres=# \df+ DBMS_RANDOM.STRING
                                                                                        List of functions
   Schema    |  Name  | Result data type |  Argument data types  | Type | Volatility | Parallel |  Owner   | Security | Access privileges | Language |    Source code     |      Descript
ion
-------------+--------+------------------+-----------------------+------+------------+----------+----------+----------+-------------------+----------+--------------------+--------------
---------
 dbms_random | string | text             | opt text, len integer | func | volatile   | unsafe   | postgres | invoker  |                   | c        | dbms_random_string | Create Random
 Strings
(1 row)

[local]:5432 postgres@postgres=# select DBMS_RANDOM.STRING('l',30) FROM GENERATE_SERIES(1,10);
             string
--------------------------------
 pthqnffkhyibnxbmcjxcjktkracldq
 ntkvkybpiinrkbplorvluewnoooqzs
 hmlsivqjkzryqbafnpwjbqnyecmtsm
 lazwshriqciibykcdysahtrurwwepp
 qbqpxiwprnszwuygxcepcmjugbqdfg
 twhjmfsjvjwojsihzgjdvmqegwgxal
 dtikdupvdkfazoshwsnfwjsmozjuwj
 gzdokhjacmkhnjwgrsyfyupqgdppym
 zfmduwkdwnqhudrrjjjiohcexjinzg
 azmmcginkfaamveemonvwcdzgbioph
(10 rows)

Summary:
Orafce插件部署到postgresql系的数据库中，有许多函数不仅在从 Oracle 迁移时有用，而且是增强 PostgreSQL 函数的方便实用程序函数，在opengauss系一样可以使用，有些自带了该插件，对于dbms_random参数记的检查是否为immutable属性。

达梦V8
达梦自带了DBMS_RANDOM.string

SQL> select DBMS_RANDOM.STRING('l',30) FROM dual connect by rownum<=10;

行号     DBMS_RANDOM.STRING('l',30)
---------- ------------------------------
1          ingfptwthrrojxjpvhmpnplbcvrnuf
2          gesnjjhhdoyuditnxpvlfkbrlenesj
3          jandnxnufqjemnmhclwywdjyuudiyw
4          sjwgmkfbgkspogecnhokglnplikprk
5          mlujsiuxjbicrxivavdohjbuaneldw
6          wpirabauajvilnhtkhqowxyytymxlp
7          vigfaghachjxqvmxqxghnefmdympxx
8          guhmahthivosufohefgkntoshrsuir
9          tpmbdmiwtqsjkopyvtfdesxslflfau
10         wtkjvowflrwfbhtqgqllupesjqxvvy

打赏

微信扫一扫，打赏作者吧～