Re: Truncate if exists

On 11 October 2012 19:59, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, Oct 10, 2012 at 3:32 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> 2) Clearly, rollout scripts benefit from not throwing errors.
>> Personally I would prefer setting SET ddl_abort_on_missing_object =
>> false; at the top of a script than having to go through every SQL
>> statement and add extra syntax. That might even help people more than
>> littering SQL with extra clauses.
>
> I've been thinking about this a bit more. It seems to me that the
> awkwardness here has a lot to do with the fact that the IF EXISTS is
> attached to the command rather than sitting outside it. We're
> basically trying to put the control logic inside the command itself,
> whereas probably what we really want is for the control logic to be
> able to exist around the command, like this:
>
> IF TABLE foo EXISTS THEN
> TRUNCATE TABLE foo;
> END IF
>
> But of course that doesn't work. I think you have to write something like this:
>
> do $$
> begin
> if (select 1 from pg_class where relname = 'foo' and
> pg_table_is_visible(oid)) then
> truncate table foo;
> end if;
> end
> $$;
>
> That is a lot more typing and it's not exactly intuitive. One obvious
> thing that would help is a function pg_table_exists(text) that would
> return true or false. But even with that there's a lot of syntactic
> sugar in there that is less than ideal: begin/end, dollar-quoting, do.
> Whatever becomes of this particular patch, I think we'd make a lot of
> people really happy if we could find a way to dispense with some of
> that stuff in simple cases.

Yeh, definitely.

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.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services