On Tue, Oct 6, 2009 at 4:43 PM, Postgres User <postgres.developer@xxxxxxxxx> wrote: > Browsing the docs last night, I realized that I've never taken > advantage of Postgres' powerful composite types. But a question came > to mind- in what scenarios should you use a composite type in a table > structure? That is, I can see the benefits of a composiite type > insofar as it essentially lets you add virtual tuples to a table > without having to alter the table structure to add new fields. > Instead you can simply extend the composite type. But why take this > approach? First, a quick clarification. You can only alter composite types if they are defined with 'create table', not 'create type as'. For this reason, I always create my composites with 'create table'. Using composites inside a table structure can be useful but is easily abused. There are useful cases though: the built in geo types are all basically composite types, in that they are grouped POD types. The rules of thumb for using composite/array inside a table are: *) Are you sure you are not adding structure for no reason? *) Does your data always get written/read at the same time? *) If you were to, say, use a foreign key, would you link it to the composite itself and not its inner fields? *) Do you not care that related changes elsewhere in the schema can give you major headaches? *) Does your data naturally group in a way such that it feels like a single element? *) Does your client app have ability to deal with composites without dealing with (much nastier than you think) parsing? If you answered 'yes' to all of the above, maybe a composite type (or, in similarly, an array) is worth considering. Composites work absolute wonders in passing data to/from functions, and can be useful for lots of other things. Just a heads up: if you are writing your application in C and are looking for an effective way of dealing with composites in the client, check out libpqtypes (http://pgfoundry.org/projects/libpqtypes/). No parsing! :-). merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
