Unnest Array to table Postgres

Question

How to unnest ARRAY to table format in Postgres These are the types that I have created

create type ngfcst.test1 as (territory character varying, territoryname character varying, parentname character varying, parentterritory character varying, lowest_level_flag character varying, count bigint);

I am doing an array_agg to return a resultset using this type. Below is a sample output from function test12:

select * from test12(
    'some_parameters');
OUTPUT (2 records):
{"(\"rec11",rec12,\"rec13",\"rec14\",rec15,1)","(\"rec21\",rec22,\"rec23\",\"rec24\",rec25,2)"}

i want the data in proper table format but doing this doesnt help :

with main as(
    select * from  test12(
    'some_parameters'))
    select unnest(o_show_proxy::ngfcst.test1[]) as unnest_col from main;

this does separate the records, but not the columns. I want the output in rows and column format as we get from below query :

select * from unnest(array[row('row11','row12','row13','row14','row15',1),
              row('row21','row22','row23','row24','row25',2)]::ngfcst.test1[])

Let me know if there is any way to get the output in rows and column format.

Your sample output is inconsistent. Please check once

Akhilesh Mishra
– Akhilesh Mishra

2021-05-21 19:16:11 +00:00
Commented May 21, 2021 at 19:16 — Akhilesh Mishra
– Akhilesh Mishra, Commented May 21, 2021 at 19:16

Akhilesh Mishra · Accepted Answer · 2021-05-21 19:19:24Z

1

Here cross join lateral will help --

Based on your example. considering your function test12() is returning proper array formation according to your user defined TYPE test1.

Try this:

with cte as(
    select * from  test12(
    'some_parameters')
)
    select t.* from cte cross join lateral unnest(cte.o_show_proxy::test1[]) t;

DEMO

answered May 21, 2021 at 19:19

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Goutam Sahoo Over a year ago

Hi Akhilesh, I have been reading up on the cross join lateral functionality and where to use it, but I am not able to understand how it works. Can you please help here

Akhilesh Mishra Over a year ago

Please read this post

Collectives™ on Stack Overflow

Unnest Array to table Postgres

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related