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

On Fri, Jan 30, 2009 at 03:45:54PM -0500, Merlin Moncure wrote:
> On 1/30/09, Sam Mason <sam@samason.me.uk> wrote:
> > I was talking about your example code:
> >
> >   INSERT INTO foo VALUES '(something)'::foo;
> >
> >  This isn't currently valid, but it sounds as though it needs to be.
> 
> hm. i don't think so...at least not quite (my thinko in orig example).
> I think per spec that would attempt to insert the constructed record
> into the first column.

bah, it would wouldn't it! why the hell was it designed like that, it's
just inviting bugs!  I'll use the following table definition below:
 create temp table foo ( a int, b text );

I was expecting:
 insert into foo values (1);

to fail in the same way as:
 insert into foo (a,b) values (1);

I've never realized before that if you leave off the column list it
guesses what you want to do.

> instead, we would want:
> 
> INSERT INTO foo(foo) VALUES ...

I'm still not sure why anyone would want to do this!

> or
> INSERT INTO foo(foo) SELECT ...

But why not just leave INSERT as it is, it works and is unambiguous!

> Assuming we didn't have a foo column, that would tell pg we are
> pushing in composite type:

I still don't see why you want to encourage people to think "have I
got a similarly named column" the whole time.  It's fine when you're
after some specific column because then you know what it's called and
you're asking for it, so when it's the same as the table it's obvious.
When it's the table you're asking for you don't want to worry about it
breaking when a column gets added.

> 'UPDATE' works simillar: SET foo =
> is the key that we are pushing composite type, not specific fields.

But why is this better than using a *?

> > I agree that the mechanism is good, it's just that the syntax you
> >  proposed comes with it's own built in footgun.  Symmetry is also muddied
> >  by the fact that SELECT and INSERT/UPDATE are built on fundamentally
> >  different premises.  It's only ever possible to modify a set of rows
> >  from one table at a time, whereas a SELECT is designed to work with
> >  multiple tables.
> 
> double-check that statement vs. example above.

I'm not sure what I'm supposed to be checking, more verbosity please!

> I just don't see the
> problem.  Only small gripe I can think of is that since you can't
> alias the table in the insert statement, if you have a column named
> 'foo', you're stuck...oh well.

I'm not sure what you are saying; is this a nail in the coffin for using
the table name as "the key" or have you just deliberately introduced the
(fuzzy, un-checked) rule that you're not allowed to have a column the
same name as the table.

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