Thread: Unnest an array in postgresql
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)"}
But it is returning me following error:
ERROR: a column definition list is only allowed for functions returning "record"
Referring to the discussion on this link: http://postgresql.nabble.com/store-multiple-rows-with-the-SELECT-INTO-statement-td5769258.html (Response by Pavel stehule)
Is there any different way to do this?
Thanks and Regards,
Piyush Jaisingkar ,
______________________________________________________________________
Disclaimer: This email and any attachments are sent in strictest confidence
for the sole use of the addressee and may contain legally privileged,
confidential, and proprietary data. If you are not the intended recipient,
please advise the sender by replying promptly to this email and then delete
and destroy this email and any attachments without any further use, copying
or forwarding.
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 string 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)"}
But it is returning me following error:
ERROR: a column definition list is only allowed for functions returning "record"
Referring to the discussion on this link: http://postgresql.nabble.com/store-multiple-rows-with-the-SELECT-INTO-statement-td5769258.html (Response by Pavel stehule)
Is there any different way to do this?
Thanks and Regards,
Piyush Jaisingkar ,
______________________________________________________________________
Disclaimer: This email and any attachments are sent in strictest confidence
for the sole use of the addressee and may contain legally privileged,
confidential, and proprietary data. If you are not the intended recipient,
please advise the sender by replying promptly to this email and then delete
and destroy this email and any attachments without any further use, copying
or forwarding.
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)"}