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

[artsolo-db]

Description

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

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:

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

Alternative

Create new functions (to avoid changing existing APIs):

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

---

Examples of Usage

Writing Nested Collections

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

Reading Nested Collections

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

-- 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.