Subscripts for nested collection does not work

[akos2]

Description

It is allowed to have a collection a as an element of other collection. But it is hard to get elements value directly

Steps to reproduce

Was tested on:

PostgreSQL 17.2 (Ubuntu 17.2-1.pgdg24.04+1) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit

DO
$$
DECLARE
t collection('bigint');
tt collection('collection');
BEGIN

t['1'] := 1;
t['2'] := 2;

tt['1'] := t;

RAISE NOTICE 'The current val of t[1] is %', t['1'];
RAISE NOTICE 'The datatype of t is %', pg_typeof(t);
RAISE NOTICE 'The datatype of tt is %', pg_typeof(tt);

RAISE NOTICE 'The datatype of t[1] is %', pg_typeof(t['1']);
RAISE NOTICE 'The datatype of tt[1] is %', pg_typeof(tt['1']);

RAISE NOTICE 'The current val of tt[1] is %', tt['1'];
RAISE NOTICE 'The current val of tt[1][1] is %', tt['1']['1'];


END
$$;

Expected outcome

Nested collection element value returned. Or provided a way to get it directly

Actual outcome

NOTICE: The current val of t[1] is 1
NOTICE: The datatype of t is collection
NOTICE: The datatype of tt is collection
NOTICE: The datatype of t[1] is bigint
NOTICE: The datatype of tt[1] is collection
NOTICE: The current val of tt[1] is {"value_type": "bigint", "entries": {"1": "1", "2": "2"}}
ERROR: collection allows only one subscript
CONTEXT: SQL expression "tt['1']['1']"
PL/pgSQL function inline_code_block line 20 at RAISE

Analysis

We see that tt is a correct collection having collection of integer as an element. If we use proxy variable, it is possible to return value:

DO
$$
DECLARE
t collection('bigint');
tt collection('collection');
x collection('bigint');
BEGIN

t['1'] := 1;
t['2'] := 2;

tt['1'] := t;

RAISE NOTICE 'The current val of t[1] is %', t['1'];
RAISE NOTICE 'The datatype of t is %', pg_typeof(t);
RAISE NOTICE 'The datatype of tt is %', pg_typeof(tt);

RAISE NOTICE 'The datatype of t[1] is %', pg_typeof(t['1']);
RAISE NOTICE 'The datatype of tt[1] is %', pg_typeof(tt['1']);

RAISE NOTICE 'The current val of tt[1] is %', tt['1'];

x := tt['1'];

RAISE NOTICE 'The current val of tt[1][1] is %', x['1'];


END
$$;

NOTICE:  The current val of t[1] is 1
NOTICE:  The datatype of t is collection
NOTICE:  The datatype of tt is collection
NOTICE:  The datatype of t[1] is bigint
NOTICE:  The datatype of tt[1] is collection
NOTICE:  The current val of tt[1] is {"value_type": "bigint", "entries": {"1": "1", "2": "2"}}
NOTICE:  The current val of tt[1][1] is 1

It would be useful to have a way to get value without additional variables and copy.
E.g., PostgreSQL allows data structures like array of arrays and has ways to work with them directly

create domain ii as int[];

DO
$$
DECLARE

a ii;
b ii[];
begin
a[1] := 1;

RAISE NOTICE 'The current val of a[1] is %', a[1];
b[1] := a;
RAISE NOTICE 'The current val of b is %', b;
RAISE NOTICE 'The current val of b[1] is %', b[1];
RAISE NOTICE 'The current val of b[1][1] is %', (b[1])[1];

end;
$$;


NOTICE:  The current val of a[1] is 1
NOTICE:  The current val of b is {"{1}"}
NOTICE:  The current val of b[1] is {1}
NOTICE:  The current val of b[1][1] is 1
;

[jim-mlodgenski]

Do we have a sense on how deeply nested people will want to have their collections? Is just a single collection of collections enough or does it need to be arbitrarily deep? It may be simple enough to overload the find function and add another parameter, but that may unnecessarily constrain what people want to do. For example, if someone wants to return a bigint from a nested collection like in the original example in this issue, it might look something like the following:

find(tt, '1', '1', null::bigint)

[artsolo-db]

It seems overloading find alone is not enough. The add function should also be extended to support nested keys, so we can read and write nested collections directly without extra variables.

Currently, reading and writing nested collections is possible, but code for 2-level or 3-level nesting becomes quite verbose. For example:

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; $$;

I would propose extending find and add to accept a variadic list of keys. For that reason, I created a new issue with examples: #32