# Function Reference

Both `collection` (text-keyed) and `icollection` (integer-keyed) support the same set of functions. The only difference is the key type: `text` for collection, `bigint` for icollection.

## collection Functions

| Function | Return Type | Description |
|---|---|---|
| `add(collection, text, text)` | `collection` | Adds a text item to a collection |
| `add(collection, text, anyelement)` | `collection` | Adds an anyelement item to a collection |
| `count(collection)` | `int4` | Returns the number of items in a collection |
| `delete(collection, text)` | `collection` | Deletes an item from a collection |
| `delete(collection)` | `collection` | Deletes all items from a collection, preserving the value type |
| `delete(collection, text, text)` | `collection` | Deletes all items with keys between lo and hi inclusive |
| `exist(collection, text)` | `bool` | Returns true if a given key exists in the collection |
| `find(collection, text)` | `text` | Returns a text item from a collection if it exists |
| `find(collection, text, anyelement)` | `anyelement` | Returns an anyelement item from a collection if it exists |
| `first(collection)` | `collection` | Sets the collection iterator to the first item |
| `last(collection)` | `collection` | Sets the collection iterator to the last item |
| `next(collection)` | `collection` | Sets the collection iterator to the next item |
| `prev(collection)` | `collection` | Sets the collection iterator to the previous item |
| `first_key(collection)` | `text` | Returns the key of the first item |
| `last_key(collection)` | `text` | Returns the key of the last item |
| `next_key(collection, text)` | `text` | Returns the key of the next item for the given key |
| `prev_key(collection, text)` | `text` | Returns the key of the previous item for the given key |
| `copy(collection)` | `collection` | Returns a copy of a collection without a context switch |
| `sort(collection)` | `collection` | Sorts by keys in collation order and points to the first item |
| `isnull(collection)` | `bool` | Returns true if the iterator has passed the end of the collection |
| `key(collection)` | `text` | Returns the key at the current iterator position |
| `value(collection)` | `text` | Returns the value as text at the current iterator position |
| `value(collection, anyelement)` | `anyelement` | Returns the value as anyelement at the current iterator position |
| `keys_to_table(collection)` | `SETOF text` | Returns all keys as a result set |
| `values_to_table(collection)` | `SETOF text` | Returns all values as text in a result set |
| `values_to_table(collection, anyelement)` | `SETOF anyelement` | Returns all values as anyelement in a result set |
| `to_table(collection)` | `TABLE(text, text)` | Returns all keys and values as text in a result set |
| `to_table(collection, anyelement)` | `TABLE(text, anyelement)` | Returns all keys and values as anyelement in a result set |
| `value_type(collection)` | `regtype` | Returns the data type of the elements within the collection |

## icollection Functions

| Function | Return Type | Description |
|---|---|---|
| `add(icollection, bigint, text)` | `icollection` | Adds a text item to an icollection |
| `add(icollection, bigint, anyelement)` | `icollection` | Adds an anyelement item to an icollection |
| `count(icollection)` | `int4` | Returns the number of items in an icollection |
| `delete(icollection, bigint)` | `icollection` | Deletes an item from an icollection |
| `delete(icollection)` | `icollection` | Deletes all items from an icollection, preserving the value type |
| `delete(icollection, bigint, bigint)` | `icollection` | Deletes all items with keys between lo and hi inclusive |
| `exist(icollection, bigint)` | `bool` | Returns true if a given key exists in the icollection |
| `find(icollection, bigint)` | `text` | Returns a text item from an icollection if it exists |
| `find(icollection, bigint, anyelement)` | `anyelement` | Returns an anyelement item from an icollection if it exists |
| `first(icollection)` | `icollection` | Sets the icollection iterator to the first item |
| `last(icollection)` | `icollection` | Sets the icollection iterator to the last item |
| `next(icollection)` | `icollection` | Sets the icollection iterator to the next item |
| `prev(icollection)` | `icollection` | Sets the icollection iterator to the previous item |
| `first_key(icollection)` | `bigint` | Returns the key of the first item |
| `last_key(icollection)` | `bigint` | Returns the key of the last item |
| `next_key(icollection, bigint)` | `bigint` | Returns the key of the next item for the given key |
| `prev_key(icollection, bigint)` | `bigint` | Returns the key of the previous item for the given key |
| `copy(icollection)` | `icollection` | Returns a copy of an icollection without a context switch |
| `sort(icollection)` | `icollection` | Sorts by keys numerically and points to the first item |
| `isnull(icollection)` | `bool` | Returns true if the iterator has passed the end of the icollection |
| `key(icollection)` | `bigint` | Returns the key at the current iterator position |
| `value(icollection)` | `text` | Returns the value as text at the current iterator position |
| `value(icollection, anyelement)` | `anyelement` | Returns the value as anyelement at the current iterator position |
| `keys_to_table(icollection)` | `SETOF bigint` | Returns all keys as a result set |
| `values_to_table(icollection)` | `SETOF text` | Returns all values as text in a result set |
| `values_to_table(icollection, anyelement)` | `SETOF anyelement` | Returns all values as anyelement in a result set |
| `to_table(icollection)` | `TABLE(bigint, text)` | Returns all keys and values as text in a result set |
| `to_table(icollection, anyelement)` | `TABLE(bigint, anyelement)` | Returns all keys and values as anyelement in a result set |
| `value_type(icollection)` | `regtype` | Returns the data type of the elements within the icollection |

## Array Interoperability

These functions convert between PostgreSQL arrays and icollections.

| Function | Return Type | Description |
|---|---|---|
| `to_icollection(anyarray)` | `icollection` | Converts a 1-D array to an icollection with 1-based keys |
| `to_array(icollection)` | `text[]` | Converts an icollection to a text array ordered by key; gaps become NULLs |
| `to_array(icollection, anyelement)` | `anyarray` | Converts an icollection to a typed array ordered by key; gaps become NULLs |

### Assignment Casts

Assignment casts are registered for common array types, allowing direct assignment in PL/pgSQL:

```sql
DECLARE v icollection;
BEGIN
  v := ARRAY[1, 2, 3];          -- int[] cast
  v := ARRAY['a', 'b', 'c'];    -- text[] cast
END;
```

Supported cast types: `int[]`, `bigint[]`, `numeric[]`, `text[]`, `boolean[]`, `float8[]`, `timestamp[]`, `timestamptz[]`.

For other array types, use `to_icollection()` explicitly:

```sql
v := to_icollection(ARRAY['2024-01-01']::date[]);
```

### BULK COLLECT Pattern

`to_icollection` enables Oracle-style BULK COLLECT INTO migration:

```sql
-- Oracle:  SELECT id BULK COLLECT INTO v_ids FROM employees;
-- pgcollection:
v_ids := to_icollection(ARRAY(SELECT id FROM employees));
```

### Notes

- `to_icollection` rejects multidimensional arrays.
- `to_array` sets the array lower bound to the collection's minimum key.
- `to_array` requires keys within the 32-bit integer range.
- Sparse key ranges (e.g., keys 1 and 1,000,000) produce large arrays with NULL gaps. Very large ranges are rejected.

## Statistics Functions

| Function / View | Description |
|---|---|
| `collection_stats` (view) | Session-level operation counters for both collection and icollection |
| `collection_stats_reset()` | Resets all statistics counters for the current session |

The `collection_stats` view columns:

| Column | Description |
|---|---|
| `add` | Number of add/assign operations |
| `context_switch` | Number of times a collection switched memory contexts |
| `delete` | Number of delete operations |
| `find` | Number of find/fetch operations |
| `sort` | Number of sort operations |
| `exist` | Number of exist checks |