0

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.

Goutam Sahoo
  • 59
  • 2
  • 9

1 Answers1

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

Akhilesh Mishra
  • 5,876
  • 3
  • 16
  • 32