Oracle index by table(Associative array) used in PostgreSQL

Postgres2015全国用户大会将于11月20至21日在北京丽亭华苑酒店召开。本次大会嘉宾阵容强大，国内顶级PostgreSQL数据库专家将悉数到场，并特邀欧洲、俄罗斯、日本、美国等国家和地区的数据库方面专家助阵:

• Postgres-XC项目的发起人铃木市一(SUZUKI Koichi)
• Postgres-XL的项目发起人Mason Sharp
• pgpool的作者石井达夫(Tatsuo Ishii)
• PG-Strom的作者海外浩平(Kaigai Kohei)
• Greenplum研发总监姚延栋
• 周正中(德哥), PostgreSQL中国用户会创始人之一
• 汪洋，平安科技数据库技术部经理
• ……

2015年度PG大象会报名地址：http://postgres2015.eventdove.com/ PostgreSQL中国社区： http://postgres.cn/ PostgreSQL专业1群： 3336901（已满） PostgreSQL专业2群： 100910388 PostgreSQL专业3群： 150657323

Oracle plSQL中支持3种集合变量, index-by table, varray, nest table。

其中index-by table是不限元素个数，支持字符串或INT下标。

用法举例：

  
  

   
   DECLARE
  
  
  
  

   
     -- Associative array indexed by string:
  
  
  
  

   
     
  
  
  
  

   
     TYPE population IS TABLE OF NUMBER  -- Associative array type
  
  
  
  

   
       INDEX BY VARCHAR2(64);            --  indexed by string
  
  
  
  

   
     
  
  
  
  

   
     city_population  population;        -- Associative array variable
  
  
  
  

   
     i  VARCHAR2(64);                    -- Scalar variable
  
  
  
  

   
     
  
  
  
  

   
   BEGIN
  
  
  
  

   
     -- Add elements (key-value pairs) to associative array:
  
  
  
  

   
    
  
  
  
  

   
     city_population('Smallville')  := 2000;
  
  
  
  

   
     city_population('Midland')     := 750000;
  
  
  
  

   
     city_population('Megalopolis') := 1000000;
  
  
  
  

   
    
  
  
  
  

   
     -- Change value associated with key 'Smallville':
  
  
  
  

   
    
  
  
  
  

   
     city_population('Smallville') := 2001;
  
  
  
  

   
    
  
  
  
  

   
     -- Print associative array:
  
  
  
  

   
    
  
  
  
  

   
     i := city_population.FIRST;  -- Get first element of array
  
  
  
  

   
    
  
  
  
  

   
     WHILE i IS NOT NULL LOOP
  
  
  
  

   
       DBMS_Output.PUT_LINE
  
  
  
  

   
         ('Population of ' || i || ' is ' || city_population(i));
  
  
  
  

   
       i := city_population.NEXT(i);  -- Get next element of array
  
  
  
  

   
     END LOOP;
  
  
  
  

   
   END;
  
  
  
  

   
   /
  
  
  
  

   
   Result:
  
  
  
  

   
   

  
  
  
  

   
   Population of Megalopolis is 1000000
  
  
  
  

   
   Population of Midland is 750000
  
  
  
  

   
   Population of Smallville is 2001
  
  
  
  

   
   Example 5-2 defines a type of associative array indexed by PLS_INTEGER and a function that returns an associative array of that type.
  
  
  
  

   
   

  
  
  
  

   
   Example 5-2 Function Returns Associative Array Indexed by PLS_INTEGER
  
  
  
  

   
   

  
  
  
  

   
   DECLARE
  
  
  
  

   
     TYPE sum_multiples IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
  
  
  
  

   
     n  PLS_INTEGER := 5;   -- number of multiples to sum for display
  
  
  
  

   
     sn PLS_INTEGER := 10;  -- number of multiples to sum
  
  
  
  

   
     m  PLS_INTEGER := 3;   -- multiple
  
  
  
  

   
   

  
  
  
  

   
     FUNCTION get_sum_multiples (
  
  
  
  

   
       multiple IN PLS_INTEGER,
  
  
  
  

   
       num      IN PLS_INTEGER
  
  
  
  

   
     ) RETURN sum_multiples
  
  
  
  

   
     IS
  
  
  
  

   
       s sum_multiples;
  
  
  
  

   
     BEGIN
  
  
  
  

   
       FOR i IN 1..num LOOP
  
  
  
  

   
         s(i) := multiple * ((i * (i + 1)) / 2);  -- sum of multiples
  
  
  
  

   
       END LOOP;
  
  
  
  

   
       RETURN s;
  
  
  
  

   
     END get_sum_multiples;
  
  
  
  

   
   

  
  
  
  

   
   BEGIN
  
  
  
  

   
     DBMS_OUTPUT.PUT_LINE (
  
  
  
  

   
       'Sum of the first ' || TO_CHAR(n) || ' multiples of ' ||
  
  
  
  

   
       TO_CHAR(m) || ' is ' || TO_CHAR(get_sum_multiples (m, sn)(n))
  
  
  
  

   
     );
  
  
  
  

   
   END;
  
  
  
  

   
   /
  
  
  
  

   
   Result:
  
  
  
  

   
   

  
  
  
  

   
   Sum of the first 5 multiples of 3 is 45
  
  

在PostgreSQL plpgsql函数中对应的用法：

PostgreSQL中不需要指定数组长度，可以存储任意个元素。

但是不能使用字符串下标。

  
  

   
   

    
    postgres=# create table t1(id int,info text);
   
   
   
   

    
    CREATE TABLE
   
   
  
  
  
  

   
   

  
  
  
  

   
   

    
    postgres=# do language plpgsql $$
   
   
   
   

    
    declare
   
   
   
   

    
      v _t1;  -- _t1表示t1表的数组类型
   
   
   
   

    
      v1 t1; 
   
   
   
   

    
    begin
   
   
   
   

    
      v1.id := 10; 
   
   
   
   

    
      v1.info := 'abc'; 
   
   
   
   

    
      v[1] := v1;
   
   
   
   

    
      v[2] := '(11,"hello")';
   
   
   
   

    
      v1.id := 100;
   
   
   
   

    
      v1.info := 'i am digoal';
   
   
   
   

    
      v[3] := v1;
   
   
   
   

    
      foreach v1 in array v loop
   
   
   
   

    
        raise notice '%, %, %', v1, v1.id, v1.info;
   
   
   
   

    
      end loop;
   
   
   
   

    
    end;
   
   
   
   

    
    $$;
   
   
  
  
  
  

   
   

    
    NOTICE:  00000: (10,abc), 10, abc
   
   
   
   

    
    LOCATION:  exec_stmt_raise, pl_exec.c:3074
   
   
   
   

    
    NOTICE:  00000: (11,hello), 11, hello
   
   
   
   

    
    LOCATION:  exec_stmt_raise, pl_exec.c:3074
   
   
   
   

    
    NOTICE:  00000: (100,"i am digoal"), 100, i am digoal
   
   
   
   

    
    LOCATION:  exec_stmt_raise, pl_exec.c:3074
   
   
   
   

    
    DO
   
   
  
  

如果要存储非结构化数据，可以使用jsonb或者hstore和数组的组合。

不过目前在plpgsql中只能对非结构化数据一次性赋值，不能部分赋值。例如：

  
  

   
   postgres=# do language plpgsql $$
  
  
  
  

   
   declare
  
  
  
  

   
     v1 hstore := '';
  
  
  
  

   
     ky text;
  
  
  
  

   
   begin
  
  
  
  

   
     v1 := v1 ||  '"a"=>1'::hstore; 
  
  
  
  

   
     v1 := v1 || hstore('b', '2'); -- 另一种hstore value写法
  
  
  
  

   
      v1 := v1 ||  '"b"=>100'::hstore;  -- 覆盖或修改b的值
  
  
  
  

   
     foreach ky in array akeys(v1) loop
  
  
  
  

   
       raise notice '%, %', ky, v1->ky;     -- hstore->key 获取指定key的value
  
  
  
  

   
     end loop;
  
  
  
  

   
   end;
  
  
  
  

   
   $$;
  
  
  
  

   
   NOTICE:  00000: a, 1
LOCATION:  exec_stmt_raise, pl_exec.c:3074
NOTICE:  00000: b, 100
LOCATION:  exec_stmt_raise, pl_exec.c:3074
DO
  
  

[参考]

1.  http://docs.oracle.com/database/121/LNPLS/composites.htm#LNPLS005