On Thu, Mar 31, 2022 at 8:02 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> At present the project seems to largely consider the IF EXISTS/IF NOT EXISTS features to have been largely a mistake
andwhile removing it is not going to happen the desire to change or extend it is not strong.
I like the IF [NOT] EXISTS stuff quite a bit. I wish it had existed
back when I was doing application programming with PostgreSQL. I would
have used it for exactly the sorts of things that Bradley mentions.
I don't know how far it's worth taking this stuff. I dislike the fact
that when you get beyond what you can do with IF [NOT] EXISTS, you're
suddenly thrown into having to write SQL against system catalog
contents which, if you're the sort of person who really likes the IF
[NOT] EXISTS commands, may well be something you don't feel terribly
comfortable doing. It's almost tempting to propose new SQL functions
just for these kinds of scripts. Like instead of adding support
for....
ALTER TABLE myschema.mytable IF EXISTS RENAME IF EXISTS this TO that;
...and I presume you need IF EXISTS twice, once for the table and once
for the column, we could instead make it possible for people to write:
IF pg_table_exists('myschema.mytable') AND
pg_table_has_column('myschema.mytable', 'this') THEN
ALTER TABLE myschema.mytable RENAME this TO that;
END IF;
An advantage of that approach is that you could also do more
complicated things that are never going to work with any number of
IF-EXISTS clauses. For example, imagine you want to rename foo to bar
and bar to baz, unless that's been done already. Well with these
functions you can just do this:
IF pg_table_has_column('mytab', 'foo') THEN
ALTER TABLE mytab RENAME bar TO baz;
ALTER TABLE mytab RENAME foo TO bar;
END;
There's no way to get there with just IF EXISTS.
--
Robert Haas
EDB: http://www.enterprisedb.com
