From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Want to json_populate_record AND save/link the source JSON text |
Date: | 2016-07-29 20:17:52 |
Message-ID: | CAKFQuwZB65vtZ0heeEORHWb-G9ttVGYSgAkv+CotwHKKYiqb6A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Given:
CREATE TABLE jsontbl ( id int, label text, json_obj json);
I need the create two rows from the following JSON, with the json_obj in
each row equal to the text/json object in the content
1,'one',{"id":1,"label":"one"}
2,'two',{"id":2,"label":"two"}
I'd like to not have to hard-code the columns ...
I've dead-ended at:
SELECT jpr.*, json_src
FROM json_array_elements($json$[ { "id":1, "label":"one" }, { "id":2,
"label":"two" } ]$json$::json) jae (json_src),
LATERAL json_populate_record(null::jsontbl, json_src) jpr
Which gets me all of the content but I'm stumped at how to send this
through "INSERT INTO jsobtbl" without knowing the columns and without
removing the ".*"
Looking for inspiration here...
It would nice to simply have a function defined:
RETURNS TABLE (out_rec anyelement, src_json json)
I can easily live with the treatment of out_rec as a composite type.
Thanks!
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Condor | 2016-07-29 20:18:54 | Re: Uber migrated from Postgres to MySQL |
Previous Message | D'Arcy J.M. Cain | 2016-07-29 20:17:08 | Re: Allowing multiple versions of PG under NetBSD |