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
;