On 11 October 2012 19:59, Robert Haas <robertmh...@gmail.com> wrote: > On Wed, Oct 10, 2012 at 3:32 AM, Simon Riggs <si...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers