Re: using composite types in insert/update

On Wed, Jan 28, 2009 at 12:03:56PM -0500, Merlin Moncure wrote:
> IMO, composite types on insert/update should work as they do on select:

> INSERT INTO foo VALUES '(something)'::foo -- fails,

The VALUES command is just a convenient way of getting lots of tuples
into PG isn't it? If the above was valid, PG would have to support
similar syntax elsewhere, which seems independent of the feature you're
really asking for.

> but we have a workaround:
> INSERT INTO foo SELECT ('(something)'::foo).* -- expands foo into foo columns

Or if you wanted to insert multiple rows:

INSERT INTO foo SELECT (txt::foo).* FROM
(VALUES ('(something)'), ('(something else)')) x(txt);

> however no such workaround exists for update. ideally,
> UPDATE foo SET foo = foo;
>
> would be valid.

Sounds useful, but seems to break existing syntax (imagine if the table
"foo" had a column called "foo"). Takahiro suggests using a * to
indicate what you're asking for and this seems to have nicer semantics
to me.

There seem to be two different improvements needed; the first would be
in allowing composite values on the RHS, the second in allowing the
column list on the LHS to be replaced with a *. E.g. we start with the
following code:

CREATE TEMP TABLE foo ( a INT, b TEXT );
INSERT INTO foo ( 1, 'a' );

the following is currently valid:

UPDATE foo SET (a,b) = (x.a,x.b) FROM (SELECT ('(2,c)'::foo).*) x;

The first step would allow you to do:

UPDATE foo SET (a,b) = x FROM (SELECT ('(2,c)'::foo).*) x;

and the second step allow you to do:

UPDATE foo SET (*) = x FROM (SELECT ('(2,c)'::foo).*) x;

> Aside from fixing a surprising behavior

Or have I missed the point and you mean the "surprising behavior" is
that you expect PG to generate WHERE clauses for you automatically.
This seems impossible in the general case.

> , it would
> greatly aid in writing triggers that do things like ship updates over
> dblink _much_ easier (in fact...the dblink_build_xxx family would
> become obsolete).
>
> e.g.
> perform dblink.dblink('UPDATE foo SET foo = \'' || new || '\'::foo);
>
> I call the existing behavior of insert/update of composite types
> broken to the point of almost being a bug. Fixing the above to work
> would close the loop on a broad new set of things you can do with
> composite types.

How well would something like this work in practice? If for some reason
"foo" had been created with the columns in a different order in the two
databases then you'd end up with things breaking pretty quickly. One
naive way out seems to be to include the column names in serialized
tuples. This has advantages (i.e. we're always told not to rely on
column order and this would be one less place we implicitly had to) as
well as disadvantages (i.e. the size of the resulting serialized value
would go up and well as the complexity of the serialization routine).

--
Sam http://samason.me.uk/