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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers