Re: Truncate if exists

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;

Maybe somewhat involved as far as code support is concerned. That said,
full integration of a PL into the main parser doesn't strike me as that
easier. Maybe a simpler way to reach the feature would be:

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'
)
EXECUTE 'TRUNCATE TABLE $1' USING target(t);

But I'm not sure it gives anything else than a hint about how to
implement the first idea.

> 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

Yes, that's the sentence that got me to think about the above proposal,
because we are already talking about implementing WITH FUNCTION in
another thread, to answer some of Pavel's needs.

> my view the goal ought to be to refine that mechanism to remove the
> clunkiness and awkwardness, rather than to invent something completely
> new.

So, what do you think? Smells like empowered SQL this time, right?

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support