Re: Best way to populate nested composite type from JSON`

On Thu, Sep 12, 2013 at 8:47 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

>
>
> Can we see a hypothetical example? json best practices for me are to
> use standard tables and than serialize/deserialize json as it goes
> through the door and not use nested composite types in your actual
> table definitions. I think it should all fit if you do it right: you
> json_[each/array_elements] the wrapping json then insert it into
> un-nested actual tables. If you must have a final destination as a
> nested composite, I think at the worst case you might have to make
> some transition composites...
>

Here is what I am thinking (roughly):

CREATE TYPE trans_line (
description text,
amount numeric,
account_id int
);

CREATE TYPE journal_entry (
reference text,
description text,
post_date date,
lines trans_line[]
);

CREATE FUNCTION save(journal_entry) RETURNS .... LANGUAGE PLPGSQL AS
$$
....
$$;

What I would like to be able to do is accept that complex data type in as a
JSON object and convert it to the record format. The data could then be
broken apart, inserted checked for validity (make sure the transaction is
balanced etc) and then inserted into the correct tables. My idea was to
create a json::journal_entry cast and use that. Otherwise I get to have
fun with row and array constructors and system tables.

However one can't pass nested JSON objects through json_populate_record().
My question is basically how to get around that.

Best wishes,
Chris Travers

>
> merlin
>

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more.shtml