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](https://docs.oracle.com/database/121/LNPLS/composites.htm#LNPLS99969) [Oracle PL/SQL Collections: Varrays, Nested & Index by Tables ](https://www.guru99.com/complex-data-types-pl-sql.html) [Collections in Oracle PL/SQL](https://oracle-base.com/articles/8i/collections-8i) [Working with Collections](https://blogs.oracle.com/oraclemagazine/working-with-collections) [Take a Dip into PostgreSQL Arrays](https://www.compose.com/articles/take-a-dip-into-postgresql-arrays/)