aws / aws/pgcollection

Subscripts for nested collection does not work

Open
#6 2 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

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

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.