>From: Jim Starkey <jas@...>
>
> Dmitry Yemanov wrote:
>
>>"Jim Starkey" <jas@...> wrote:
>>
>>>First, I'd like to ammend my proposal with the addition of
>>>
>>> upgrade user <username> ...
>>>
>>>The "upgrade user" command is syntactically parallel to "create user",
>>>and is semantically equivalent to "alter user" if the named user exists
>>>and semantically equivalent to "create user" if not. The upgrade form
>>>allows a single script to both create and update accounts.
>>
>>We already have RECREATE and CREATE OR ALTER statements. AFAIU, the latter
>>one is exactly your UPGRADE statement, but it doesn't introduce additional
>>reserved words. Let's be consistent with the existing DDL.

As I already mentioned some time ago, my opinion is that
RECREATE is actually not an optimal design. I would prefer
MySQL-style IF [NOT] EXISTS [CREATE|DROP], or even the
ability to mix PSQL with DDL, giving
IF (NOT EXISTS table) THEN CREATE TABLE ...
This is more useful because gives more flexibility to my
scripts. During the lifecycle of must (my, at least)
products I sometimes want to add tables/fields, sometimes
to drop them -- all preferably without any errors from sql
script execution.

>First, upgrade has different semantics than recreate.
> Following
> recreation of a table, the table is empty. After an upgrade of a table,
> the data is unchanged.
>
> Upgrade is an enormously useful verb. It allows a single script to be
> used to both initially and upgrade an application's meta data.

For exactly the same reasons as RECREATE, I also think
that UPGRADE is too restricted.

> Key to making this all work is specific intelligence built into the
> upgrade verbs. An upgrade of a table, for example, adds but not drop
> fields and increases, never shortens, character field lengths, etc.

Yes, and there are times when I *do want* to drop,
shorten, and rename fields. You shall say that I am
creating myself a maintenance nightmare, but it is my foot
to shoot, so please let me ;-)

So, much more orthogonal and general solution would be to
allow full-blown set of alter table (or, in the case of
users, ALTER USER) statements, executed in transactional
context. This allows trivial modelling of UPGRADE
functionality you described, plus many extra use cases.
---
Professional hosting for everyone - http://www.host.ru