Re: using composite types in insert/update

On 1/30/09, Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:
> On Fri, Jan 30, 2009 at 03:12:27PM -0500, Merlin Moncure wrote:
> > On 1/30/09, Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:
> > > quite often (i.e. a VALUES command with many singletons). This seems
> > > a bit annoying and appears to be what you were suggesting you wanted
> > > before (although you killed the relevant bit of context, making me think
> > > we may be talking about different things).
> >
> > we are. See the title of the thread: 'using composite types in
> > insert/update'. that's what I'm talking about. I especially am not
> > talking about the 'values' statement.
>
>
> Humm, 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. instead, we would want:

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

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

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

> 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 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 don't think
INSERT INTO foo f(f) VALUES ...
or
INSERT INTO foo(f) f VALUES ...
are worth exploring.

merlin