On Fri, Apr 15, 2011 at 4:27 PM, Kevin Grittner <kevin.gritt...@wicourts.gov> wrote: > Merlin Moncure <mmonc...@gmail.com> wrote: > >> There are lots of use cases for this. I use composite types to >> marshal data to the client all the time, and recursive structures >> are fairly common in many classic problems. Recursive composites >> fit the bill perfectly. > > I'm trying to get my head around why SQL composite types are a good > way to marshal complex object graphs with recursion. I can see > where it could be done, I'm still not convinced that it's better > than SQL passing out data in tabular form with relationship > established by matching values. In other words, when is it a good > idea to do the relation to object mapping in the database engine?
You can certainly do that. however that requires the client to put take the data and immediately put it in relational-ish data structures so you can browse the data properly. That's a popular approach, albeit expensive and error prone. If the database sends it back to you constructed, you can just stupldly iterate over the returned set and process it as you go (you could in fact stream the data directly off the protocol of the client library supports it). arrays, composites, etc. are just ways of setting up ad hoc structures for passing. they serve the same purpose (at least, they way I use them) as xml or json documents getting ripped directly in and out of the database. maybe it's a good idea to nest data that way going and out, and maybe it isn't, but it's fast and effective. xml and json solve the problem but introduce a completely difference set of headaches because they are not tightly coupled to the database...and slow. Consider we also have to send data to the database. I can recursively wrap up data in the client using libpqtypes, fire it to a receiving function which unnests it and processes it. This is a couple of orders of magnitude faster than streaming it in over multiple queries. I could for example, send in a entire bill of materials assembly in a single query. I don't consider that hacky, in fact I think a lot of classic SQL approaches are in fact hacks around not being able to do things that way. merlin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
