在 Greenplum 数据库开发中,经常会遇到诸如行转列、单行变多行等需求,熟练使用 Greenplum 自带的函数,实现这些功能会变得得心应手。

表结构设计

  • 表创建

GP 系统表中保存的表名都是以小写保存。通常 SQL 语句中表名对大小写不敏感,但是不允许在建表语句中使用双引号("")包括表名,这样会影响系统表中存储的名称,使得表名存在大小写或特殊字符。

数据类型

序号 类型 存储空间 描述
1 integer 4 字节 常用的整数
2 numeric 变长 用户声明精度,精确
3 varchar(n) 变长字符,有长度限制
4 text 变长字符,无长度限制
5 date 4 字节 只用于表示日期
6 timestamp 8 字节 日期和时间,不带时区
7 timestamptz 8 字节 日期和时间,带时区
8 interval 12 字节 时间间隔

rownum 伪列

Oracle 中常用的 rownum 在 Greenplum 中没有,可以通过开窗函数模拟类似 rownum 的列来使用。

postgres=# select row_number() over() as rownum, * from emp;

 rownum | empno | ename  |    job    | mgr  |  hiredate  |   sal   |  comm   | deptno
--------+-------+--------+-----------+------+------------+---------+---------+--------
      1 |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |         |     20
      2 |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |         |     20
      3 |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |         |     30
      4 |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30
      5 |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |         |     20
      6 |  7839 | KING   | PRESIDENT |      | 1981-11-17 | 5000.00 |         |     10
      7 |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30
      8 |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30
      9 |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |         |     20
     10 |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |         |     30
     11 |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |         |     10
     12 |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30
     13 |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |         |     10
     14 |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |         |     20
(14 rows)

字符串拆分

单行变多行

  • regexp_split_to_table
postgres=# select row_number() over() as rownum, * from (select regexp_split_to_table('A001/D/200198', '/') as param) t;

 rownum | param
--------+--------
      1 | A001
      2 | D
      3 | 200198


postgres=# select row_number() over() as rownum, * from (select regexp_split_to_table('苹果,橘子,葡萄', ',') as param) t;

 rownum | param
--------+-------
      1 | 苹果
      2 | 橘子
      3 | 葡萄
  • regexp_split_to_table 字段操作
postgres=# create table test_str(id int, str varchar(100)) distributed by (id);

postgres=# insert into test_str values(1, 'hello|world');

postgres=# insert into test_str values(2, 'database|greenplum|system');

postgres=# select * from test_str;
 id |            str
----+---------------------------
  2 | database|greenplum|system
  1 | hello|world

postgres=# select id, regexp_split_to_table(str, E'\\|') str from test_str;
 id |    str
----+-----------
  1 | hello
  1 | world
  2 | database
  2 | greenplum
  2 | system

注:由于 | 字符被转义了,因此想正确表达,必须对其进行转义。

  • string_to_array
postgres=# select unnest(string_to_array('A001,D,200198', ',')) param;
 param
--------
 A001
 D
 200198

单行变多列

  • split_part
postgres=# select 
  split_part('A001,D,200198', ',', 1) param1,
  split_part('A001,D,200198', ',', 2) param2,
  split_part('A001,D,200198', ',', 3) param3;

 param1 | param2 | param3
--------+--------+--------
 A001   | D      | 200198

多行变单行

  • string_agg
postgres=# create table test_str(id int, str varchar(20)) distributed by (id);

postgres=# insert into test_str values(1, 'hello');

postgres=# insert into test_str values(1, 'world');

postgres=# insert into test_str values(2, 'greenplum');

postgres=# insert into test_str values(2, 'database');

postgres=# insert into test_str values(2, 'system');

postgres=# select * from test_str;
 id |    str
----+-----------
  1 | hello
  1 | world
  2 | greenplum
  2 | database
  2 | system

postgres=# select id, string_agg(str, ',' order by str) from test_str group by id order by id;
 id |        string_agg
----+---------------------------
  1 | hello,world
  2 | database,greenplum,system

行转列

  • decode
postgres=# with fruit as (
  select 1 id, '苹果' name, 1000 nums
  union all
  select 2 id, '苹果' name, 2000 nums 
  union all
  select 3 id, '橘子' name, 5000 nums
  union all   
  select 4 id, '葡萄' name, 3500 nums
  union all  
  select 5 id, '芒果' name, 4200 nums
)
select * from fruit;  

 id | name | nums
----+------+------
  1 | 苹果 | 1000
  2 | 苹果 | 2000
  3 | 橘子 | 5000
  4 | 葡萄 | 3500
  5 | 芒果 | 4200


postgres=# with fruit as (
  select 1 id, '苹果' name, 1000 nums
  union all
  select 2 id, '苹果' name, 2000 nums 
  union all
  select 3 id, '橘子' name, 5000 nums
  union all   
  select 4 id, '葡萄' name, 3500 nums
  union all  
  select 5 id, '芒果' name, 4200 nums
)
select 
  sum(decode(name, '苹果', nums)) as apple,
  sum(decode(name, '橘子', nums)) as orange,
  sum(decode(name, '葡萄', nums)) as grape,
  sum(decode(name, '芒果', nums)) as mango
from fruit; 

 apple | orange | grape | mango
-------+--------+-------+-------
  3000 |   5000 |  3500 |  4200

列转行

  • unnest(array)
postgres=# with fruit as (
  select 1 id, '苹果' name, 1000 q1, 2000 q2, 3300 q3, 5000 q4
  union all
  select 2 id, '橘子' name, 3000 q1, 3000 q2, 3200 q3, 1500 q4
  union all
  select 3 id, '香蕉' name, 2500 q1, 3500 q2, 2200 q3, 2500 q4
  union all
  select 4 id, '葡萄' name, 1500 q1, 2500 q2, 1200 q3, 3500 q4
)
select * from fruit;

 id | name |  q1  |  q2  |  q3  |  q4
----+------+------+------+------+------
  1 | 苹果 | 1000 | 2000 | 3300 | 5000
  2 | 橘子 | 3000 | 3000 | 3200 | 1500
  3 | 香蕉 | 2500 | 3500 | 2200 | 2500
  4 | 葡萄 | 1500 | 2500 | 1200 | 3500


postgres=# with fruit as (
  select 1 id, '苹果' name, 1000 q1, 2000 q2, 3300 q3, 5000 q4
  union all
  select 2 id, '橘子' name, 3000 q1, 3000 q2, 3200 q3, 1500 q4
  union all
  select 3 id, '香蕉' name, 2500 q1, 3500 q2, 2200 q3, 2500 q4
  union all
  select 4 id, '葡萄' name, 1500 q1, 2500 q2, 1200 q3, 3500 q4
)
select 
  id,                                              -- 聚合列
  name,                                            -- 聚合列
  unnest(array['Q1', 'Q2','Q3', 'Q4']) as colname, -- 自定义名称,与需转换的列数量一致
  unnest(array[q1, q2, q3, q4])        as colvalue -- 需转换的列
FROM fruit;

 id | name | colname | colvalue
----+------+---------+----------
  1 | 苹果 | Q1      |     1000
  1 | 苹果 | Q2      |     2000
  1 | 苹果 | Q3      |     3300
  1 | 苹果 | Q4      |     5000
  2 | 橘子 | Q1      |     3000
  2 | 橘子 | Q2      |     3000
  2 | 橘子 | Q3      |     3200
  2 | 橘子 | Q4      |     1500
  3 | 香蕉 | Q1      |     2500
  3 | 香蕉 | Q2      |     3500
  3 | 香蕉 | Q3      |     2200
  3 | 香蕉 | Q4      |     2500
  4 | 葡萄 | Q1      |     1500
  4 | 葡萄 | Q2      |     2500
  4 | 葡萄 | Q3      |     1200
  4 | 葡萄 | Q4      |     3500

集合操作

并集

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

  • UNION 默认会去重,即重复数据只会返回一行
postgres=# select * from test1;
 id |  name
----+--------
  1 | Apple
  2 | Orange

postgres=# select * from test2;
 id |  name
----+--------
  1 | Apple
  3 | Banana

postgres=# select * from test1 union select * from test2 order by 1;
 id |  name
----+--------
  1 | Apple
  2 | Orange
  3 | Banana
  • UNION ALL 会保留重复数据,返回所有行
postgres=# select * from test1 union all select * from test2 order by 1;
 id |  name
----+--------
  1 | Apple
  1 | Apple
  2 | Orange
  3 | Banana

交集

  • INTERSECT 两个查询共有的记录
postgres=# select * from test1 intersect select * from test2;
 id | name
----+-------
  1 | Apple

差集

  • EXCEPT 第一个查询的结果集减去第二个查询的结果集
postgres=# select * from test1 except select * from test2;
 id |  name
----+--------
  2 | Orange

GET DIAGNOSTICS

PostgreSQL PL/pgSQL 命令 GET DIAGNOSTICS 可用于检索有关当前执行状态的信息。

get [ current ] diagnostics variable { = | := } item [ , ... ];

例如:

get diagnostics integer_var = row_count;  

捕获运行过程中的状态值

序号 名称 类型 描述
1 row_count bigint 最近的 SQL 命令处理的行数
2 pg_context 变长 描述当前调用堆栈的文本行
  • 最近的 SQL 命令处理的行数
create table fruits as 
select * from (values (1, 'banana'), (2, 'pear'), (3, 'apple')) t (id, name)
distributed by (id);


create or replace function update_fruits()
    returns setof fruits 
    language plpgsql
as $function$
declare
  rows integer;
begin 
    update fruits set id = id + 1;
    get diagnostics rows = row_count;
    return query select null::int, format ('%s rows updated', rows);
    return query select * from fruits order by id;
    get diagnostics rows = row_count;
    return query select null::int, format ('%s rows retrieved', rows);
end;
$function$;


postgres=# select * from scott.update_fruits();
 id |       name
----+------------------
    | 3 rows updated
  2 | banana
  3 | pear
  4 | apple
    | 3 rows retrieved
  • 描述当前调用堆栈的文本行
create or replace function inner_func()
    returns text language plpgsql 
as $function$
declare 
    stack   text;
begin 
    get diagnostics stack = pg_context;
    raise notice E'---Call Stack---\n%', stack;
    return 'Succeed...';
end;
$function$;


create or replace function outer_func() 
    returns text 
    language plpgsql 
as $function$
begin
    return inner_func();
end;
$function$; 


postgres=# select scott.outer_func();
NOTICE:  ---Call Stack---
PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS
PL/pgSQL function outer_func() line 3 at RETURN
CONTEXT:  PL/pgSQL function outer_func() line 3 at RETURN
 outer_func
------------
 Succeed...

捕获异常时的 STACK 内容

序号 名称 类型 描述
1 returned_sqlstate text 异常的 sqlstate 错误代码
2 message_text text 异常的主要消息的文本
3 pg_exception_context text 描述异常时调用堆栈的文本行
create or replace function scott.use_diagnostics()
    returns void language plpgsql
as $function$
declare 
    v_state   text;
    v_msg     text;
    v_context text;
begin 
    create table test(id int) distributed by(id);
    create table test(id int) distributed by(id);
exception
    when others then 
        get stacked diagnostics
            v_state   = returned_sqlstate,
            v_msg     = message_text,
            v_context = pg_exception_context;
    raise exception E'got exception:
            state  : %
            message: %', v_state, v_msg || ', ' || v_context;
end;
$function$;


postgres=# SELECT scott.use_diagnostics();
ERROR:  got exception:
                        state  : 42P07
                        message: relation "test" already exists, SQL statement "create table test(id int) distributed by(id)"
PL/pgSQL function use_diagnostics() line 8 at SQL statement

存储过程日志记录

  • 创建存储过程日志记录表
CREATE TABLE RT_PUB_PROC_LOGS (
  VC_ID          VARCHAR2(100) DEFAULT SYS_GUID() NOT NULL,
  VC_OWNER       VARCHAR2(50),
  VC_MODULE_NAME VARCHAR2(50) DEFAULT 'PROCEDURE',
  VC_PROC_NAME   VARCHAR2(50),
  D_STARTDATE    DATE,
  D_ENDATE       DATE,
  C_PARAMS       CLOB,
  C_PARAMS2      CLOB,
  VC_SQLCODE     VARCHAR2(50),
  VC_SQLERRM     VARCHAR2(2000),
  VC_ERR_CONTENT VARCHAR2(2000),
  T_STARTTIME    TIMESTAMP(6),
  T_ENDTIME      TIMESTAMP(6),
  F_ELAPSEDTIME  INTEGER,
  VC_STATUS      VARCHAR2(2),
  D_UPDATETIME   DATE DEFAULT SYSDATE
);

COMMENT ON TABLE MPUB_F_PROC_LOGS
  IS '数据处理日志记录表';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_ID
  IS '唯一标识';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_OWNER
  IS '所属用户';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_MODULE_NAME
  IS '模块名称';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_PROC_NAME
  IS '模块代码';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.D_STARTDATE
  IS '模块参数(开始日期)';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.D_ENDATE
  IS '模块参数(结束日期)';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.C_PARAMS
  IS '模块参数(组合代码、计划代码...)';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.C_PARAMS2
  IS '预留模块参数,可为空';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_SQLCODE
  IS 'SQLCODE';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_SQLERRM
  IS 'SQLERRM';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_ERR_CONTENT
  IS '异常信息';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.T_STARTTIME
  IS '程序开始时间';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.T_ENDTIME
  IS '程序结束时间';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.F_ELAPSEDTIME
  IS '总耗时(毫秒)';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_STATUS
  IS '运行状态(1 正在运行;0 结束;-1 异常)';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.D_UPDATETIME
  IS '更新时间';

ALTER TABLE MPUB_F_PROC_LOGS
  ADD CONSTRAINT PK_MPUB_F_PROC_LOGS PRIMARY KEY (VC_ID);
  • 创建用于记录时间的函数
CREATE OR REPLACE FUNCTION F_TIMESTAMP_DIFF(I_STARTTIME IN TIMESTAMP,
                                            I_ENDTIME   IN TIMESTAMP)
  RETURN INTEGER AS
  /****************************************************************\
  *                           xxxxxx
  *           COPYRIGHT (C) 2020, xxxxxx
  * ===============================================================
  *
  * 名     称:F_TIMESTAMP_DIFF
  * 功能 描述:存储过程运行时间
  *
  * 参     数:I_START_TIME    开始时间
  *            I_END_TIME      结束时间
  *
  * 修订 记录:
  * 版本号   编辑时间      编辑人           修改描述
  * 1.0.0    2020-03-10    xxx              创建此函数
  \****************************************************************/
  STR       VARCHAR2(50);
  MISECOND  INTEGER;       --毫秒
  SECONDS   INTEGER;       --秒
  MINUTES   INTEGER;       --分
  HOURS     INTEGER;       --小时
  DAYS      INTEGER;       --天
BEGIN
  STR       := TO_CHAR(I_ENDTIME - I_STARTTIME);
  MISECOND  := TO_NUMBER(SUBSTR(STR, INSTR(STR, ' ') + 10, 3));
  SECONDS   := TO_NUMBER(SUBSTR(STR, INSTR(STR, ' ') + 7, 2));
  MINUTES   := TO_NUMBER(SUBSTR(STR, INSTR(STR, ' ') + 4, 2));
  HOURS     := TO_NUMBER(SUBSTR(STR, INSTR(STR, ' ') + 1, 2));
  DAYS      := TO_NUMBER(SUBSTR(STR, 1, INSTR(STR, ' ')));

  RETURN DAYS*24*60*60*1000 + HOURS*60*60*1000 + MINUTES*60*1000 + SECONDS*1000 + MISECOND;

END;
  • 创建日志记录存储过程
CREATE OR REPLACE PROCEDURE P_MPUB_F_PROC_LOGS(I_ID          IN VARCHAR2,
                                               I_OWNER       IN VARCHAR2 DEFAULT NULL,
                                               I_MODULE_NAME IN VARCHAR2 DEFAULT NULL,
                                               I_PROC_NAME   IN VARCHAR2 DEFAULT NULL,
                                               I_STARTDATE   IN DATE     DEFAULT NULL,
                                               I_ENDDATE     IN DATE     DEFAULT NULL,
                                               I_SQLCODE     IN NUMBER   DEFAULT NULL,
                                               I_SQLERRM     IN VARCHAR2 DEFAULT NULL,
                                               I_ERR_CONTENT IN VARCHAR2 DEFAULT NULL,
                                               I_PARAMS      IN CLOB     DEFAULT NULL,
                                               I_PARAMS2     IN CLOB     DEFAULT NULL,
                                               I_STATUS      IN VARCHAR2) IS
  /****************************************************************\
  *                           xxxxxx
  *           COPYRIGHT (C) 2020, xxxxx
  * ===============================================================
  *
  * 名     称:P_MPUB_F_PROC_LOGS
  * 功能 描述:存储过程日期记录
  *
  * 参     数:I_USER_NAME     用户名
  *            I_MODULE_NAME   模块分类,默认为 'PROCEDURE'
  *            I_PROC_NAME     模块名称
  *            I_STARTDATE     模块参数(开始日期)
  *            I_ENDDATE       模块参数(结束日期)
  *            I_PARAMS        模块参数(组合代码、计划代码...)
  *            I_PARAMS2       预留模块参数,可为空
  *            I_SQLCODE       SQLCODE
  *            I_SQLERRM       SQLERRM
  *            I_ERR_CONTENT   异常信息
  *            I_STATUS        模块运行状态(1 正在运行;0 结束;-1 异常)
  *
  * 修订 记录:
  * 版本号   编辑时间      编辑人           修改描述
  * 1.0.0    2020-03-10    xxx              创建此存储过程
  \****************************************************************/
  V_STARTTIME   TIMESTAMP;   --开始时间
  V_ENDTIME     TIMESTAMP;   --结束时间
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

  --程序运行中...
  IF I_STATUS = '1' THEN

    SELECT SYSTIMESTAMP INTO V_STARTTIME FROM DUAL;

    INSERT INTO MPUB_F_PROC_LOGS (
      VC_ID,
      VC_OWNER,
      VC_MODULE_NAME,
      VC_PROC_NAME,
      D_STARTDATE,
      D_ENDATE,
      VC_SQLCODE,
      VC_SQLERRM,
      VC_ERR_CONTENT,
      C_PARAMS,
      C_PARAMS2,
      T_STARTTIME,
      T_ENDTIME,
      F_ELAPSEDTIME,
      VC_STATUS,
      D_UPDATETIME
    )
    SELECT
      I_ID,
      I_OWNER,
      I_MODULE_NAME,
      I_PROC_NAME,
      I_STARTDATE,
      I_ENDDATE,
      NULL,
      NULL,
      NULL,
      I_PARAMS,
      I_PARAMS2,
      V_STARTTIME,
      NULL,
      NULL,
      I_STATUS,
      SYSDATE
    FROM DUAL;

  --程序结束...
  ELSIF I_STATUS = '0' THEN

    SELECT SYSTIMESTAMP INTO V_ENDTIME FROM DUAL;
    SELECT T_STARTTIME INTO V_STARTTIME FROM MPUB_F_PROC_LOGS WHERE VC_ID = I_ID;

    UPDATE MPUB_F_PROC_LOGS S
    SET S.VC_SQLCODE     = I_SQLCODE,
        S.VC_SQLERRM     = I_SQLERRM,
        S.T_ENDTIME     = V_ENDTIME,
        S.F_ELAPSEDTIME  = F_TIMESTAMP_DIFF(V_ENDTIME, V_STARTTIME),
        S.VC_STATUS      = I_STATUS
    WHERE S.VC_ID = I_ID;

  --程序异常...
  ELSIF I_STATUS = '-1' THEN

    UPDATE MPUB_F_PROC_LOGS S
    SET S.VC_SQLCODE     = I_SQLCODE,
        S.VC_SQLERRM     = I_SQLERRM,
        S.VC_ERR_CONTENT = I_ERR_CONTENT,
        S.VC_STATUS      = I_STATUS
    WHERE S.VC_ID = I_ID;

  END IF;

  COMMIT;

EXCEPTION
 WHEN OTHERS THEN
  ROLLBACK;

END P_MPUB_F_PROC_LOGS;
  • 主存储过程中调用日志记录存储过程
PROCEDURE P_TEST(I_STARTDATE IN  VARCHAR2,
                 I_ENDDATE   IN  VARCHAR2,
                 I_PARAM     IN  CLOB,
                 O_ERRCODE   OUT INTEGER,
                 O_ERRMSG    OUT VARCHAR2) IS
  /*****************************************************************************\
  *                           xxxxxx
  *           COPYRIGHT (C) 2020, xxxxxx
  * ===========================================================================
  *
  * 名     称:P_TEST
  * 功能 描述:xxxxx
  *
  * 参     数:传入 -> xxxxx
  *            传出 -> xxxxx
  *
  * 修订 记录:
  * 版本号   编辑时间      编辑人           修改描述
  * 1.0.0    2020-02-28    Li.chaoxiang     创建此存储过程
  \*****************************************************************************/
  V_STARTDATE DATE;          --开始日期
  V_ENDDATE   DATE;          --结束日期
  V_OWNER     VARCHAR2(20);  --所属用户
  V_ID        VARCHAR2(100); --唯一标识
BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD''';
  O_ERRCODE   := 0;
  O_ERRMSG    := '运行成功';
  V_STARTDATE := TO_DATE(I_STARTDATE, 'YYYY-MM-DD');
  V_ENDDATE   := TO_DATE(I_ENDDATE, 'YYYY-MM-DD');

  --获取所属用户
  SELECT USERNAME INTO V_OWNER FROM USER_USERS;

  WHILE (V_LOOPDATE <= V_ENDDATE) LOOP

   --获取唯一标识
   SELECT SYS_GUID() INTO V_ID FROM DUAL;

   --程序开始,写入日志信息
   P_MPUB_F_PROC_LOGS(I_ID          => V_ID,
                      I_OWNER       => V_OWNER,
                      I_MODULE_NAME => 'PROCEDURE',
                      I_PROC_NAME   => 'P_TEST',
                      I_STARTDATE   => V_STARTDATE,
                      I_ENDDATE     => V_ENDDATE,
                      I_PARAMS      => V_FUNDCODE,
                      I_STATUS      => '1');


   --程序结束,更新状态
   P_MPUB_F_PROC_LOGS(I_ID      => V_ID,
                      I_SQLCODE => O_ERRCODE,
                      I_SQLERRM => O_ERRMSG,
                      I_STATUS  => '0');

   V_LOOPDATE := V_LOOPDATE + 1;

  END LOOP;

  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
   --程序异常,写入日志信息
   P_MPUB_F_PROC_LOGS(I_ID          => V_ID,
                      I_SQLCODE     => SQLCODE,
                      I_SQLERRM     => SQLERRM,
                      I_ERR_CONTENT => SUBSTR('未处理异常:' || SQLERRM || '。' ||
                                              DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
                                              1,
                                              1000),
                      I_STATUS      => '-1');
   ROLLBACK;
   O_ERRCODE := SQLCODE;
   O_ERRMSG  := SUBSTR('未处理异常:' || SQLERRM || '。' ||
                       DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
                       1,
                       1000);

END P_TEST;

删除重复记录

在 GreenPlum 中,表中有两个列是系统分配的,gp_segment_id(分配在哪个节点上),ctid(在每个节点上是唯一的),因此可以利用这两个字段删除数据。

  • 构造数据
postgres=# create table scott.t_emp as select * from scott.emp distributed by (empno);

postgres=# insert into t_emp select * from emp limit 5;
  • 查看重复记录(其中 empno = 7521 重复)
postgres=# select gp_segment_id, ctid, * from t_emp order by empno;

 gp_segment_id |  ctid  | empno | ename  |    job    | mgr  |  hiredate  |   sal   |  comm   | deptno
---------------+--------+-------+--------+-----------+------+------------+---------+---------+--------
             1 | (0,1)  |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |         |     20
             0 | (0,1)  |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30
             2 | (0,6)  |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30
             2 | (0,1)  |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30
             1 | (0,2)  |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |         |     20
  • 删除重复记录
postgres=# delete from t_emp 
where ctid in (
  select max(ctid) from t_emp group by empno having count(1) > 1
);

随机数

生成随机数字

  • 生成 0 到 1 之间的随机数
postgres=# select random();
       random
---------------------
 0.00576547440141439

该函数返回的数据类型为 double precision,每次调用都会返回不同的结果。

  • 生成指定范围内的随机数
# 生成一个大于等于 low,小于 high 的随机数
low + random() * (high - low)

# 生成一个大于等于 10 且小于 30 的随机数
postgres=# select 10 + random() * (30 - 10) random;
      random
------------------
 29.9697697721422
  • 生成某个范围内的随机整数
# floor(low + random() * (high - low))

# 生成 100 以内的随机整数
postgres=# select floor(0 + random() * (100 - 0)) random;
 random
--------
     19
  • 生成 6 位数字手机验证码
postgres=# select to_char(random() * 1000000, '099999') as captcha;
 captcha
---------
  051033

生成随机字符串

Greenplum 没有提供专门生成随机字符串的函数,但是可以通过其他函数进行模拟。

  • 使用 chr 函数,生成固定长度的随机字符串
postgres=# select chr(floor(random() * 26)::integer + 65);
 chr
-----
 P
  • 创建函数
create or replace function random_string (
  num integer,
  chars text default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
) 
  returns text
  language plpgsql strict
as $function$
declare
  res_str text := '';
begin
  if num < 1 then
      raise exception 'invalid length';
  end if;
  for __ in 1..num loop
    res_str := res_str || substr(chars, floor(random() * length(chars))::int + 1, 1);
  end loop;
  return res_str;
end;
$function$;
  • 生成一个由字母和数字组成,长度为 10 的随机字符串
postgres=# select random_string(10);
 random_string
---------------
 irXuzT961z
  • 指定随机长度参数,生成一个长度大于等于 10 且小于等于 20 的随机字符串
postgres=# select random_string(floor(10 + random() * 11)::int);
 random_string
---------------
 OarmButFct6w

生成 UUID

UUID(Universal Unique Identifier)或者 GUID(Globally Unique Identifier)是一个 128 比特(Bit) 的数字,可以用于唯一标识每个网络对象或资源。由于它的生成机制,一个 UUID 可以保证几乎不会与其他 UUID 重复,因此常常用于生成数据库中的主键值。

  • 启用加/解密的扩展模块 pgcrypto
postgres=# create extension pgcrypto;
  • 使用函数返回一个 UUID
postgres=# select gen_random_uuid();
           gen_random_uuid
--------------------------------------
 12adb859-b97d-45e3-8762-33d4a8e54d5b
  • 该函数返回的数据类型为 uuid,如果想要生成没有 - 的 UUID 字符串,可以使用 REPLACE 函数
postgres=# select replace(gen_random_uuid()::text, '-', '');
             replace
----------------------------------
 955f86bb43d6472b8ac9d7857cb4aedc
  • 将 UUID 添加到表
create table fruit (
  id   uuid default gen_random_uuid() primary key,
  name varchar(10)
);

postgres=# insert into fruit(name) values ('Apple');

postgres=# select * from fruit;
                  id                  | name
--------------------------------------+-------
 0bbe0aad-6154-4357-98e1-5308c0eae318 | Apple

自动递增主键

在 Greenplum 中,通过将数据类型设置为 SERIAL 来创建自动递增的键。

create table fruit (
  id   serial,
  name varchar(10)
) distributed by (id);

postgres=# insert into fruit(name) values ('Apple');
postgres=# insert into fruit(name) values ('Orange');

postgres=# select * from fruit order by id;
 id |  name
----+--------
  1 | Apple
  2 | Orange
  • 删除 id = 2 的记录
postgres=# delete from fruit where id = 2;
  • 重新插入一条记录
postgres=# insert into fruit(name) values ('Orange');

postgres=# select * from fruit order by id;
 id |  name
----+--------
  1 | Apple
  3 | Orange

空值处理函数

序号 函数 描述
1 nullif(exp1, exp2) 如果 exp1 等于 exp2,则返回空;否则返回 exp1
2 coalesce(exp1, exp2 ... expn) 返回列表中第1个不为空的值,如果全部元素为空,则返回空
  • nullif
postgres=# select nullif(3, 2);
 nullif
--------
      3


postgres=# select nullif(3, 3);
 nullif
--------


postgres=# select nullif(1, null);
 nullif
--------
      1


postgres=# select nullif(null, null);
 nullif
--------
  • coalesce
postgres=# select coalesce(null, 1, null, 2, 3);
 coalesce
----------
        1


postgres=# select coalesce(null, 0);
 coalesce
----------
        0


postgres=# select coalesce(null, 'AAA');
 coalesce
----------
 AAA

单引号字符串转义

执行 insert 语句时,若字符串中带有单引号,会报错,就需要转义之后才能插入。

insert into test values (1,'''test-name''');

连乘

原创文章,转载请注明出处:http://www.opcoder.cn/article/61/