Re: Unnest an array in postgresql - Mailing list pgsql-general

From David G. Johnston
Subject Re: Unnest an array in postgresql
Date
Msg-id CAKFQuwbEkAihEXgkXXf-LHNPvx-N63VvHQOEgpmU2C5S79ufHg@mail.gmail.com
Whole thread Raw
In response to Unnest an array in postgresql  ("Jaisingkar, Piyush" <Piyush.Jaisingkar@nttdata.com>)
List pgsql-general
On Thu, Oct 20, 2016 at 2:40 AM, Jaisingkar, Piyush <Piyush.Jaisingkar@nttdata.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.

pgsql-general by date:

Previous
From: Scott Marlowe
Date:
Subject: Re: Strange? BETWEEN behaviour.
Next
From: John R Pierce
Date:
Subject: Re: Postgresql apt repository naming scheme question