Greetings!
I am writing a set of SQL statements that will create tables that may be required for one of our test programs. I would like to clean out any pre-existing versions of these tables and the sequences they use, but I don't know if those pre-existing things will actually exist. If I issue a command like
DROP SEQUENCE this_sequence_does_not_exist;
then an exception is raised and no other commands in my SQL file will be executed.
I tried using an if statement combined with the EXISTS() function:
if exists(select 1 from this_sequence_does_not_exist) then
drop sequence this_sequence_does_not_exist;
end if;
but in a plain SQL window, the "if" statement does not seem to work. Even if it did, I would still get an exception when Postgresql tries to access the nonexistent sequence.
The only thing that seems to have a chance of working is to create a function, and then inside that function used a BEGIN/EXCEPTION construct, and then drop the function after I'm finished with it. But this seems to be an awfully roundabout way of doing this.
What is the recommended way of doing this?
Thank you very much.
RobR