aws / aws/pgcollection

Support Nested Collections in `add` and `find` Functions

Open
#32 0 comments 0 reactions 0 assignees View on GitHub
enhancement
Dominant language
C
Stars
24
Forks
5
PR merge metrics
No merged PRs in 30d

Description

## **Description**

Currently, `pgCollection` supports storing collections inside collections. However, reading and writing nested collections is verbose for 2-level, 3-level, or deeper structures:

```sql
DO $$
DECLARE
arr_instance collection('collection');
var1 text;
BEGIN
-- Write a 3-level nested array
arr_instance := add(arr_instance, 'A',
add(CASE WHEN exist(arr_instance, 'A') THEN find(arr_instance, 'A', NULL::collection) ELSE NULL::collection END, 'AA',
add(CASE WHEN exist(arr_instance, 'A') AND exist(find(arr_instance, 'A', NULL::collection), 'AA')
THEN find(find(arr_instance, 'A', NULL::collection), 'AA', NULL::collection)
ELSE NULL::collection END, 'AAA', 'some text'::text)));

-- Read a 3-level nested array
var1 := (find(find(arr_instance, 'A', NULL::collection), 'AA', NULL::collection))['AAA']::text;

RAISE NOTICE 'var1: %', var1;
END $$;
```

This becomes cumbersome for 2-level, 3-level, or deeper nested collections.

---

## **Proposal**

### Main Proposal (preferred)

Extend the existing `add` and `find` functions to accept a **variadic list of keys**, enabling direct read/write of nested collections:

```sql
add(collection, value, VARIADIC keys text[])
find(collection, dummy, VARIADIC keys text[])
```

### Alternative

Create new functions (to avoid changing existing APIs):

```sql
add2(collection, value, VARIADIC keys text[])
find2(collection, dummy, VARIADIC keys text[])
```

---

## **Examples of Usage**

### Writing Nested Collections

```sql
DO $$
DECLARE
arr collection('collection');
BEGIN
arr := add(arr, 'Hello', 'A','B','C'); -- 3-level nested write
END $$;
```

### Reading Nested Collections

```sql
DO $$
DECLARE
arr collection('collection');
val text;
BEGIN
val := find(arr, NULL::text, 'A','B','C'); -- 3-level nested read
END $$;
```

---

## **Implementation (hidden)**

Click to expand the proposed `add` and `find` VARIADIC implementations

```sql
-- find: traverse delegating to built-in find()
CREATE OR REPLACE FUNCTION find(coll collection, dummy anyelement, VARIADIC keys text[])
RETURNS anyelement
LANGUAGE plpgsql
AS $$
DECLARE
cur_coll collection := coll;
key text;
n int := coalesce(array_length(keys,1), 0);
i int;
BEGIN
IF n = 0 THEN
RETURN NULL;
END IF;

IF n > 1 THEN
FOR i IN 1 .. n-1 LOOP
key := keys[i];
-- delegate to built-in find - preserves its error behavior
cur_coll := find(cur_coll, key, NULL::collection);
END LOOP;
END IF;

key := keys[n];
RETURN find(cur_coll, key, dummy);
END;
$$;

-- add: recursive, builds/merges nested collections correctly
CREATE OR REPLACE FUNCTION add(coll collection, val anyelement, VARIADIC keys text[])
RETURNS collection
LANGUAGE plpgsql
AS $$
DECLARE
n int := coalesce(array_length(keys,1),0);
first_key text;
rest_keys text[];
child collection;
base_coll collection := coalesce(coll, NULL::collection);
BEGIN
IF n = 0 THEN
RAISE EXCEPTION 'add: no keys provided';
END IF;

-- base case: single key -> use built-in add(collection, key, value)
IF n = 1 THEN
RETURN add(base_coll, keys[1], val);
END IF;

-- prepare first key and rest slice
first_key := keys[1];
rest_keys := keys[2:n];

-- get existing child (if present), otherwise start with NULL::collection
IF exist(base_coll, first_key) THEN
child := find(base_coll, first_key, NULL::collection);
ELSE
child := NULL::collection;
END IF;

-- update the child recursively
child := add(child, val, VARIADIC rest_keys);

-- put updated child back into parent collection
RETURN add(base_coll, first_key, child);
END;
$$;
```

---

## **Pros**

* Allows direct reading and writing of nested collections.
* Makes code cleaner and more readable for deep nesting.

---

## **Cons**

* Changing the sequence of input parameters for `add` and `find` may break existing code (if modifying the original functions).
* Using new functions (`add2`/`find2`) avoids breaking existing code but adds similar APIs.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.