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/)

Oracle vs PostgreSQL Develop（17） - ARRAY

相關文章