I'm nearly ready to commit a patch that adds support for INSERT and
UPDATE assignments to individual fields of composite columns,
along the lines of
UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;
INSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);
This turned out to be a rather more interesting project than I'd
expected when I started, because it had a lot of interactions with
the existing support for updating elements of arrays. I believe
I've got things all worked out so that the two things play together;
you can do weird & wonderful stuff like
UPDATE foo SET col.subfld1.subfld2[2] = x, col.subfld2.y = z;
and it all works. However, I was forced to confront the fact that
our existing semantics for INSERT to an array member are just plain
bizarre. If you look at the existing "arrays" regression test you
will discover that you can do
INSERT INTO tab (arraycol[subscripts]) VALUES (something);
but *the subscripts make absolutely no difference in the result*.
In fact, all three of the INSERTs in that test that do this are
wrong, because the provided subscripts disagree with the dimensionality
of the supplied data. The only reason this regression test ever
passed is that the subscript values given in the INSERT target list
are ignored.
What I would like to do about this is define INSERT to a subscripted
column name as working the same way that an assignment to a element or
slice of a zero-dimension array presently does --- that is, you get an
actual array back and not a NULL. It would also fall out that UPDATE
of a null array value would behave that way.
This is an area that no one's been very happy about in the past, so
I'm not expecting a lot of push-back on this proposal, but I thought
I'd better toss it out and see if anyone complains...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
