Re: Truncate if exists

On 10/12/2012 11:05 PM, Christopher Browne wrote:
> 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;
We could even go as far as

{ DDL STATEMENT } IF CONDITION ELSE {ANOTHER DDL STATEMENT };

For example

CREATE TABLE mytable(...)
IF NOT EXISTS TABLE mytable
ELSE TRUNCATE mytable;

>
> 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.