By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table test(id int);
insert into test values(1);
insert into test values(1);
1 rows affected
1 rows affected
select * , row_number() over (partition by id ) from test
| id | row_number |
|---|---|
| 1 | 1 |
| 1 | 2 |
create type test1 as (territory character varying, territoryname character varying, parentname character varying, parentterritory character varying, lowest_level_flag character varying, count bigint);
with cte as (
select '{"(\"rec11\",\"rec12\",\"rec13\",\"rec14\",\"rec15\",1)","(\"rec21\",\"rec22\",\"rec23\",\"rec24\",\"rec25\",2)"}' as "o_show_proxy"
)
select t.* from cte cross join lateral unnest(cte.o_show_proxy::test1[]) t
| territory | territoryname | parentname | parentterritory | lowest_level_flag | count |
|---|---|---|---|---|---|
| rec11 | rec12 | rec13 | rec14 | rec15 | 1 |
| rec21 | rec22 | rec23 | rec24 | rec25 | 2 |