You can add BULK COLLECT to the RETURNING INTO clause of a DELETE, INSERT, or UPDATE command:
For information on the RETURNING INTO clause, see Using the RETURNING INTO clause. insert, update, and delete are the same as the INSERT, UPDATE, and DELETE commands described in INSERT, UPDATE, and DELETE.
If you specify a single collection, then collection can be a collection of a single field, or it can be a collection of a record type. If you specify more than one collection, then each collection must consist of a single field. The expressions following the RETURNING keyword must match all fields in the target collections in number, order, and type-compatibility. Specifying * returns all columns in the affected table.
Note
The use of * is an EDB Postgres Advanced Server extension and isn't compatible with Oracle databases.
The clerkemp table created by copying the emp table is used in the examples that follow.
Examples
This example increases all employee salaries by 1.5, stores the employees’ numbers, names, and new salaries in three associative arrays, and displays the contents of these arrays:
This example uses a single collection defined with a record type to store the employees’ numbers, names, and new salaries:
This example deletes all rows from the clerkemp table and returns information on the deleted rows into an associative array. It then displays the array.