Subscripts for nested collection does not work
- Dominant language
- C
- Stars
- 24
- Forks
- 5
- PR merge metrics
- No merged PRs in 30d
Description
## 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_
1.
```
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
;
```
Contributor guide
Assessment
This issue has not been assessed yet.