On 12/2/15 10:38 PM, Kaare Rasmussen wrote:
On 2015-12-03 01:04, Jim Nasby wrote:
We have a client that has a similar (though also a bit different)
need. Specifically, they get an XML document that has element
attributes that tell you what data type the element should contain. We
convert the XML to JSON (easy thanks to plpython), which produces a
bunch of nested JSON objects (typed as specifically as possible in
JSON). The XML attributes get turned into items in an object. So
OK, AFAIUI, you added the schema to each row. I think that I have fewer
variations, so perhaps the information would live better outside, but
that's a detail. Turning them into tables and views is a good way to
represent the indexable data. Functionally, it seems to me to be almost
the same as functional indexing, but much more transparent, and easier
to write a query for,
I didn't add the schema; in this case the schema was always the same. If
you had a limited number of schemas you could indicate which one was in
a particular document and use the appropriate decoding.
Are you in control of the JSON itself, and are the number of
permutations known in advance? It might be that something like table
inheritance is a better solution...
Yes, I can alter the db specification. Not sure how table inheritance
would help, though?
They provide a means where you can refer to the common parts of
disparate schemas in one place, while being able to deal with the inner
details on each child table.
It might not be useful depending on what your goals are. I mentioned it
because I think most people only think of inheritance as "That weird
thing that partitioning uses."
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
