Re: using composite types in insert/update

On Fri, Jan 30, 2009 at 11:01:53AM -0500, Merlin Moncure wrote:
> On 1/30/09, Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:
> > 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 this has nothing to do with the VALUES command! Going back to what
I interpret as your original point, I find myself wanting to write:

SELECT *
FROM foo f, (VALUES 1, 5, 7, 23, 47) v
WHERE f.id = v;

but end up having to write the following:

SELECT *
FROM foo f, (VALUES (1), (5), (7), (23), (47)) x(v)
WHERE f.id = x.v;

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

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

For several reasons; mainly because SQL is an abortion of a language,
it's got no regularity and attempts to justify requirements because of
"symmetry" will end up causing more headaches.

Another way of saying what you seem to be saying above is: I want things
to work correctly, unless I happen to have a column name that happens to
be the same as the table at which point I want everything to break.

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

Record *types* are most definitely not first class objects;
record/composite *values* on the other hand have been gaining support
for a while. There are a few weirdo's left, like VALUES commands only
working with records, but the dichotomy between record and "non-record"
types is slowly vanishing.

> > UPDATE foo SET (*) = x FROM (SELECT ('(2,c)'::foo).*) x;
>
> Hm. IMO, set (*) is a completely new invention of what '*' means.

In my head, * has always meant all the columns associated with some
record. This is just putting it in a new place in the grammar. It's
nice because it doesn't introduce any ambiguities, whereas using the
table name does.

I'm not sure if the brackets are needed, but I thought it safer to leave
them in.

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

Then blame the original designers of SQL; they optimized the syntax for
a different set of use cases! Is the symmetry more obvious when you
compare:

SELECT * FROM foo;

with

UPDATE foo SET * = x;

?

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