On Mon, Oct 15, 2012 at 3:14 PM, Dimitri Fontaine <dimi...@2ndquadrant.fr> wrote: > Robert Haas <robertmh...@gmail.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?" -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers