Re: Truncate if exists

On Mon, Oct 15, 2012 at 12:53 PM, Christopher Browne <cbbrowne(at)gmail(dot)com> wrote:
> The places where *I* care about this are places where performance is
> almost entirely irrelevant to the question.
>
> When I'm writing 'scripts' that are doing this kind of thing, I'm
> doing schema 'surgery', and, within reason, it's not particularly
> performance sensitive. I'm much more worried about DDL scripts being
> repeatable and manageable than I am about them being fast.
>
> So I'm going to elide the performance bits.
>
> Robert, when you first tossed out the notion of:
>
> do $$
> begin
> if (select 1 from pg_class where relname = 'foo' and
> pg_table_is_visible(oid)) then
> truncate table foo;
> end if;
> end
> $$;
>
> my first reaction was "Ick! Why am I switching languages (e.g. -
> from plain SQL to pl/pgsql), and running functions to do this?!?"
>
> In retrospect, your later comments make it pretty clear that you're
> not proposing that as the end state, just that that's the
> functionality that needs to be run.

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.

I think the invention of DO was a big step in the right direction,
because before that if you wanted procedural logic in your script, you
had to create a function, call it, and then drop the function. That
is exceedingly awkward and introduces a number of unpleasant and
unnecessary failure modes. With DO, you can write the logic you want
as an SQL statement, it's just a clunky and awkward SQL statement. In
my view the goal ought to be to refine that mechanism to remove the
clunkiness and awkwardness, rather than to invent something completely
new.

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