Oracle vs PostgreSQL Develop（17） - ARRAY

PostgreSQL可用ARRAY来替代Oracle中的collection type,包括associative array/Varrays (Variable-Size Arrays)/Nested Tables

Oracle
简单举个例子:

drop table if exists employee;create table employee(id int,name varchar(30),department varchar(30),salary float);insert into employee(id,name,department,salary) select rownum,substrb(object_name,1,30),substrb(object_name,1,30),1000 from dba_objects;DECLARE   TYPE EmpTabTyp IS TABLE OF employee%ROWTYPE      INDEX BY PLS_INTEGER;   emp_tab EmpTabTyp;   i int := 0;BEGIN   /* Retrieve employee record. */   for c1 in (select * from employee) loop     emp_tab(i).id := c1.id;     emp_tab(i).name := c1.name;     emp_tab(i).department := c1.department;     emp_tab(i).salary := c1.salary;     i := i+1;   end loop;   -- SELECT * INTO emp_tab(100) FROM employee WHERE id = 100;END;/

更简单的做法是使用bulk collection

DECLARE   TYPE EmpTabTyp IS TABLE OF employee%ROWTYPE      INDEX BY PLS_INTEGER;   emp_tab EmpTabTyp;   i int := 0;BEGIN   /* Retrieve employee record. */   select id,name,department,salary bulk collect into emp_tab from employee;END;/

PostgreSQL
使用ARRAY

drop type record_of_employee;CREATE TYPE record_of_employee AS (id int,name varchar(30),department varchar(30),salary float);do$$declare  employees record_of_employee[];begin  select array_agg(employee) into employees from employee limit 1;  raise notice 'id is %',employees[1].id;  raise notice 'name is %',employees[1].name;end$$;

对于Associative array indexed by string,PG的数组则替代不了.

DECLARE  -- Associative array indexed by string:  TYPE population IS TABLE OF NUMBER  -- Associative array type    INDEX BY VARCHAR2(64);            --  indexed by string...

参考资料
PL/SQL Collections and Records
Oracle PL/SQL Collections: Varrays, Nested & Index by Tables
Collections in Oracle PL/SQL
Working with Collections
Take a Dip into PostgreSQL Arrays