Want to json_populate_record AND save/link the source JSON text

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.

Browse pgsql-general by date

  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