Re: Unnest an array in postgresql

On Thu, Oct 20, 2016 at 2:40 AM, Jaisingkar, Piyush <
Piyush(dot)Jaisingkar(at)nttdata(dot)com> wrote:

> Hello,
>
>
>
>
>
> I am trying to run following query in a function:
>
>
>
>
>
> CREATE TEMP TABLE temptable on commit drop as (Select * from
> unnest(string_to_array(temp1,',')) as (rep_id int,install_uprn
> varchar,address text,postcode varchar));
>
>
>
> Where temp1 is an array and looks like this:
>
>
>
>
>
> {"(20812,,BND11TN-H1,PL-I1)","(20859,,BND11TN-H1,PL-I1)","(
> 20867,,BND11TN-H1,PL-I1)","(20884,,BND11TN-H1,PL-I1)","(
> 20894,,BND11TN-H1,PL-I1)","(20912,,BND11TN-H1,PL-I1)"}
>

The result of string_to_array here is "text[]" which gets unnested into a
single "text" column.

​The first thing I would do is:

CREATE TYPE tp [...]

Then figure out how to construct a: "tp[]​"

Unnesting "tp[]" will then just work.

David J.