Re: using composite types in insert/update - Mailing list pgsql-hackers

On 1/30/09, Sam Mason <sam@samason.me.uk> wrote:
> 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:
>
> 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.

You are missing the point, using the composite type allows you to
build the insert without knowing the specific layout of the
table...only the table itself and the fields that comprise the key for
update statements.

>  > 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.

I don't think it would...right now select statements work the way I
want.  If there is table and column with the same name, the column
name is assumed.  It's an issue of symmetry...why can't you insert the
same way you select?

By the way, record types are virtually first class objects starting with 8.4:
create index foo_idx on foo(foo);
select (1,2)::foo = (3,4)::foo;
select foo from foo order by foo;
select foo::text::foo;  -- got this in 8.3

are all valid.

>  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;

Hm. IMO, set (*) is a completely new invention of what '*' means.  I
guess it's ok though, but I think the composite type is more natural.
I think if you went this route you should think about other places
that this syntax might be valid.  I'm not arguing against what you're
saying, but the composite type should work too.

>  > 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.

The surprising behavior is that 'select foo from foo' works, but
'update foo set foo = x::foo' does not.

>  > , 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

That's a separate application specific issue that applies only to
dblink style replication (and I don't think keeping to schemas similar
is really all that difficult).  Composite type insertion has
usefulness far beyond dblink triggers.  Imagine a global trigger that
captures record to text and logs to text table.  Now it's trivial if
you want to render the text back into the table, since you don't have
to look up the field list to generate the statement.

>  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).
>

That's nice as well, but should be considered separately from powering
composite types.

merlin