Re: drop if exists

Andrew Dunstan wrote:

>
>
> Alvaro Herrera wrote:
>
>> Andrew Dunstan wrote:
>>
>>
>>> From time to time the suggestion crops up of allowing a DROP IF
>>> EXISTS ... syntax. This seems not unreasonable, and I just spent a
>>> few minutes looking at what might be involved.
>>>
>>
>>
>> What about "CREATE IF NOT EXISTS" (CINE)? If we support DROP IF EXISTS
>> (DIE), is the other one going to be supported too?
>>
>>
>
> Maybe. But I am not sure they need to be done together.
>
>> How does this play with schemas? I assume DIE drops the table in any
>> schema in the search path. What if there's more than one; drop the
>> first one?
>
>
> Yes. Just like now. My idea was that at the point where it currently
> errors out because the object exists, we would instead simply fall
> through and take no action.
>
>> CINE creates the schema in the first schema in the path,
>> just like CREATE.
>>
>>
>> Also, DIE does not need to lock the table afterwards because it won't
>> exist, but CINE needs to keep a lock until transaction commit.
>>
>>
>
> Right. That's one reason I thought of starting with the DIE case ;-)
>
> cheers
>
> andrew
>
>
In real world scenarios having "CREATE IF NOT EXISTS" or "drop if
exists" on basic objects is just not enough.
I'll try to explain:

when writing sql scripts for "database upgrade", I very often have this
case:

1. check if object exists at all, if not, create it
2. Check if table is of the last version (does it have all required fields)
3. If table is older version, then create new fields, constraints etc...
4. Sometimes even I have to do something with the data or table
structure depending on
some database setting (data from my settings table)
etc...

So, if only tables, functions and other base objects are supported by
new syntax, I'll still
have to write temporary functions or use pgbash (like I do now).
The really good thing would be to have implemented "IF" statement in
general, but I understand that this is
not a trivial task at all.

Just my two cents..

Regards !