Re: Truncate if exists

On Mon, Oct 15, 2012 at 3:14 PM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> if (select 1 from pg_class where relname = 'foo' and
>>> pg_table_is_visible(oid)) then
>>> truncate table foo;
>>> end if;
>>
>> Yeah, I think the functionality that we need is pretty much there
>> already today. What we need to do is to get the syntax to a point
>> where people can write the code they want to write without getting
>> tangled up by it.
>
> What about continuing to extend on that incredibly useful WITH syntax we
> already have:
>
> WITH target AS (
> SELECT oid::regclass AS t
> FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
> WHERE pg_table_is_visible(oid)
> AND nspname = 'public' AND NOT relname ~ 'exclude-pattern'
> )
> TRUNCATE TABLE t FROM target;

This still seems to be trying rather too hard.

The original suggestion was that, given the original query:

truncate table public.foo;

that we add syntax to make the request optional:

truncate table if exists public.foo;

Throwing in $$, oid, pg_class, joins, and such all seem like way more
syntax than we started with.

There are only so many 'clean' ways to modify the truncate request:

a) We could augment TRUNCATE with an "IF EXISTS" modifier, as
described in the initial patch.

b) Perhaps the IF EXIST might fit well afterwards, or be reversed somehow.

truncate table unless not exists public.foo;
truncate table public.foo if exists;
truncate table where exists public.foo;

c) My proposal was to add in a more generic modifier that wouldn't be
specific to TRUNCATE.

Thus:

truncate table public.foo if exists table public.foo;

That's a *little* longer than what's in b), but this would allow
extending the conditional to any kind of statement, which seems like a
more powerful idea to me. It would also support doing other actions
on the same conditional basis:

insert into bar (select id, name from public.foo)
if exists table public.foo;

If you want a more "prefix-y" version, well, here's how it might look
using a leading WITH clause:

with exists table public.foo
truncate public.foo;

with exists table public.foo
insert into bar (select id, name from public.foo);

I don't terribly much like that. I think I'd rather use WHEN than WITH.

when exists table public.foo
truncate public.foo;

when exists table public.foo
insert into bar (select id, name from public.foo);

That does seem a bit nicer than the { STATEMENT } if (conditional)
idea. And nary a $$, oid, or pg_class to be seen.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"