On Thu, Sep 12, 2013 at 8:47 AM, Merlin Moncure <mmoncure@xxxxxxxxx> 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.
