CODEWITHSUNDEEP
sqlarrayspostgresql
Goutam Sahoo
Goutam Sahoo

Reputation: 59

Unnest Array to table Postgres

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.

Upvotes: 0

Views: 1051

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

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

Upvotes: 1

Related Questions