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