Re: Truncate if exists

On Fri, Oct 12, 2012 at 3:04 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> So we just need a function called pg_if_table_exists(table, SQL) which
>> wraps a test in a subtransaction.
>>
>> And you write
>>
>> SELECT pg_if_table_exists('foo', 'TRUNCATE TABLE foo');
>>
>> and we can even get rid of all that other DDL crud that's been added....
>>
>> and we can have pg_if_table_not_exists() also.
>
> You could make this more composable by having pg_table_exists() and
> pg_execute_sql_from_string(). Then you can write: SELECT CASE WHEN
> pg_table_exists(...) THEN pg_execute_sql_from_string(...) END. And if
> you want the if-not-exists case then just stick a NOT in there. And
> if you want a more complicated condition, you can easily write that as
> well.

While that certainly has the merit of being compact, it mixes kinds of
evaluation (e.g. - parts of it are parsed at different times) and
requires quoting that isn't true for the other sorts of "IF EXISTS"
queries.

To be sure, you can do anything you like inside a DO $$ $$ language
plpgsql; block, but it's not nice to have to do a lot of work
involving embedding code between languages. Makes it harder to
manipulate, analyze, and verify.

Let me observe that Perl has, as one of its conditional concepts, the
notion of a "statement modifier"
<http://perldoc.perl.org/perlsyn.html#Statement-Modifiers>, which
corresponds pretty much to the IF EXISTS/IF NOT EXISTS modifiers that
have gotten added to Postgres over the last few versions. (I *think*
statement modifiers are attributable to SNOBOL, not 100% sure. I'm
pretty sure it predates Perl.)

I suggest the though of embracing statement modifiers in DDL, with
some options possible:
a) { DDL STATEMENT } IF CONDITION;
b) { DDL STATEMENT } UNLESS CONDITION;

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

That feels like a cleaner extension than what we have had, with the IF
EXISTS/IF NOT EXISTS clauses that have been added to various
CREATE/DROP/ALTER commands.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"