Re: CREATE SCHEMA IF NOT EXISTS

This case points to a weakness in many programming languages, not having a clear ifof (if and only if) versus if construction.

The sane use case for create schema foo if not exists <object> is for building a database dynamically, where several points may be the first to put a table in a schema, and schemas should not be created if there are no objects. The create/search/drop design pattern having its own problems.

Thus the construction should default to one behavior, and have an option for the second.

e.g.

create schema foo if not exists (will not be done if foo existed)
create schema foo if not exists FORCE (will be done even if foo existed)

This would even allow for mixed e.g.

create schema foo if not exists (tables that should be created once and not again)
FORCE (objects routine will add if the schema does)

On Oct 2, 2012, at 6:33 PM, Fabrízio de Royes Mello wrote:

>
> 2012/10/2 Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
> Excerpts from Andrew Dunstan's message of mar oct 02 17:24:38 -0300 2012:
> >
> > On 10/02/2012 03:48 PM, Tom Lane wrote:
> > > Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
>
> > >> Well, if that's the rationale then you end up with no schema foo at all
> > >> (i.e. both die), which seems even more surprising (though I admit it has
> > >> the advantage of being a simple rule to document.)
> > > I think we should just disallow putting any contained objects in the
> > > statement when IF NOT EXISTS is used. It's simple to understand, simple
> > > to document and implement, and I think it covers all the sane use-cases
> > > anyway.
> >
> > I thought we'd already agreed on this.
>
> Well, it's not what the latest proposed patch implements.
>
>
> You're right... the latest proposed patch don't implements it.
>
> I'll change the patch and send soon...
>
> Regards,
>
> --
> Fabrízio de Royes Mello
> Consultoria/Coaching PostgreSQL
> >> Blog sobre TI: http://fabriziomello.blogspot.com
> >> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
> >> Twitter: http://twitter.com/fabriziomello
>