Postgresql数组与Oracle嵌套表的使用区别

oracle_0">oracle中的多维数组

Oracle中常说的数组就是嵌套表，下面给出两个多维使用实例，引出和PG的差异：

一维赋值（第一行给1列）

set serveroutput on;
declare
  type arr_num is table of number;
  type arr_arr_num is table of arr_num;
  arr arr_num:= arr_num();
  arrarr arr_arr_num:= arr_arr_num();
begin
    DBMS_OUTPUT.PUT_LINE('====================');
    DBMS_OUTPUT.PUT_LINE(arr.COUNT);
    DBMS_OUTPUT.PUT_LINE(arrarr.COUNT);
    arr.extend;
    arrarr.extend;
    DBMS_OUTPUT.PUT_LINE(arr.COUNT);
    DBMS_OUTPUT.PUT_LINE(arrarr.COUNT);
    arrarr(1) := arr;
    arrarr(1)(1) := 1;
    DBMS_OUTPUT.PUT_LINE('====================');
    FOR i IN 1..arrarr.COUNT 
    LOOP
      DBMS_OUTPUT.PUT('line ' || i || ':');
      for j in 1..arrarr(i).count
      loop
        DBMS_OUTPUT.PUT('|' || arrarr(i)(j) || '|');
      end loop;
      DBMS_OUTPUT.PUT_LINE('');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('====================');
end;
/

结果

====================
0
0
1
1
====================
line 1:|1|
====================

二维赋值（第二行给3列）


set serveroutput on;
declare
  type arr_num is table of number;
  type arr_arr_num is table of arr_num;
  arr arr_num:= arr_num();
  arrarr arr_arr_num:= arr_arr_num();
begin
    DBMS_OUTPUT.PUT_LINE('====================');
    DBMS_OUTPUT.PUT_LINE(arr.COUNT);
    DBMS_OUTPUT.PUT_LINE(arrarr.COUNT);
    arr.extend;
    arrarr.extend;
    DBMS_OUTPUT.PUT_LINE(arr.COUNT);
    DBMS_OUTPUT.PUT_LINE(arrarr.COUNT);
    arrarr(1) := arr;
    arrarr(1)(1) := 1;
    DBMS_OUTPUT.PUT_LINE('====================');
    FOR i IN 1..arrarr.COUNT 
    LOOP
      DBMS_OUTPUT.PUT('line ' || i || ':');
      for j in 1..arrarr(i).count
      loop
        DBMS_OUTPUT.PUT('|' || arrarr(i)(j) || '|');
      end loop;
      DBMS_OUTPUT.PUT_LINE('');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('====================');
    arr.extend;
    arr.extend;
    arrarr.extend;
    arrarr(2) := arr;
    arrarr(2)(1) := 11;
    arrarr(2)(2) := 21;
    arrarr(2)(3) := 31;

    DBMS_OUTPUT.PUT_LINE('line 1 count: ' || arrarr(1).count);
    DBMS_OUTPUT.PUT_LINE('line 2 count: ' || arrarr(2).count);
    DBMS_OUTPUT.PUT_LINE('====================');
    FOR i IN 1..arrarr.COUNT 
    LOOP
      DBMS_OUTPUT.PUT('line ' || i || ':');
      for j in 1..arrarr(i).count
      loop
        DBMS_OUTPUT.PUT('|' || arrarr(i)(j) || '|');
      end loop;
      DBMS_OUTPUT.PUT_LINE(CHR(10));
    END LOOP;
end;
/

结果

====================
0
0
1
1
====================
line 1:|1|
====================
line 1 count: 1
line 2 count: 3
====================
line 1:|1|

line 2:|11||21||31|

在这里插入图片描述
可以看到，oracle的嵌套表中数据只要类型匹配，数据没有维度的对应关系：

也就是在下述实例中：

  type arr_num is table of number;
  type arr_arr_num is table of arr_num;

arr_arr_num类型的元素中的任意一个位置，都可以保存arr_num类型，无论arr_num类型中有几个元素。

例如上面实例中最终arr_num的数据存储：

arrarr{ 
    arr{ 1 }, 
    arr{ 11, 21, 31 }, 
}

arrarr(0).count == 1
arrarr(1).count == 3

Postgresql中的多维数组

PG中没有oracle中的嵌套表，往往会把PG的数组概念对应到Oracle的嵌套表上，因为数据逻辑存储形式都表现为数组。

但是除了语法上的差异外，与Oracle一个重大的差异就是PG中的多维数组维度必须统一，也就是每一行的列数必须相同，例如：

postgres=# select ARRAY[[1,2,3],[11,21,31]];
        array         
----------------------
 {{1,2,3},{11,21,31}}
(1 row)

postgres=# select ARRAY[[1],[11,21,31]];
ERROR:  multidimensional arrays must have array expressions with matching dimensions
postgres=#

而oracle中的嵌套表，可以做到第一行是[1]，第二行是[11,21,31]，推测oracle的嵌套表类型是完全独立的一套类型系统，用指针数组实现，类似于C语言中的指针数组，使用比较灵活。

arrarr = [*p1, *p2]
 *p1 : [1]
 *p2 : [11,21,31]

所以把Oracle的嵌套表搬到PG上还是有些麻烦的，大部分功能应该都没有对标替换的方法，最好在内核支持。

下面介绍一些PG数组基操：

数组基本操作

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');


SELECT * FROM sal_emp;

postgres=# SELECT * FROM sal_emp;
 name  |      pay_by_quarter       |                 schedule                  
-------+---------------------------+-------------------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)

数组读

postgres=# SELECT pay_by_quarter FROM sal_emp;
      pay_by_quarter       
---------------------------
 {10000,10000,10000,10000}
 {20000,25000,25000,25000}
(2 rows)

postgres=# SELECT pay_by_quarter[3] FROM sal_emp;
 pay_by_quarter 
----------------
          10000
          25000

数组读切片

postgres=# SELECT schedule FROM sal_emp WHERE name = 'Bill';
                 schedule                  
-------------------------------------------
 {{meeting,lunch},{training,presentation}}
(1 row)

postgres=# SELECT schedule[1:2] FROM sal_emp WHERE name = 'Bill';
                 schedule                  
-------------------------------------------
 {{meeting,lunch},{training,presentation}}
(1 row)

postgres=# SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
        schedule        
------------------------
 {{meeting},{training}}