Re: Truncate if exists

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.

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