Re: Truncate if exists

On Fri, Oct 12, 2012 at 5:52 PM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> Christopher Browne <cbbrowne(at)gmail(dot)com> writes:
>> I suggest the though of embracing statement modifiers in DDL, with
>> some options possible:
>> a) { DDL STATEMENT } IF CONDITION;
>> b) { DDL STATEMENT } UNLESS CONDITION;
>
> Just saying. I hate that. Makes it harder to read, that last bit at the
> end of the command changes it all. It's cool for a linguist, I guess,
> but we're not typing sentences at the psql prompt…
>
>> where CONDITION has several possible forms:
>> i) {IF|UNLESS} ( SQL expression returning T/F )
>> ii) {IF|UNLESS} {EXISTS|NOT EXISTS}
>> {TABLE|SCHEMA|COLUMN|FUNCTION|...} object_name
>
> Now we're talking about T-SQL? Sorry, I suppose you didn't mean to lead
> us that way, but I couldn't resist comparing. Soon enough you want a
> full programming language there.

To be perfectly frank, I think that's exactly where we ought to be
going. Oracle and Microsoft both did it, so why are we convinced it's
a bad idea? One of the huge problems with PL/pgsql is that every SQL
expression in there has to be passed to the executor separately, which
is painfully slow. It frequently doesn't matter because writing loops
in a procedural language is often the wrong approach anyway, but it is
not always the wrong approach and people sometimes do it even when it
is, and then they end up unhappy.

In the short term this is not a practical outcome for us; what we can
reasonably do is add a few convenience functions to what we already
have to make it easy to test for things like the presence of a table,
the presence of a column, the presence of a schema, etc. But in the
longer term, this is definitely something that people want. Being
able to wrap control-flow statements around SQL is fundamentally
useful, which is why every major database supports it. Being able to
do it without a lot of superfluous syntactic sugar and with good
performance is even more useful.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company