Thanks to Mark, I tried once more and eventually somehow got altering all views to "select 1 as test from rdb$database" working and then could delete them.
As I have many different versions of my schema in the field I am not exactly sure which dependencies excactly I will come across. So I wrote this PSQL Block, that iterates through all these objects ignoring errors until everything is cleaned up. So if any of these objects is not deletable I break out of the iteration once it has run 100 times to avoid hangs. After that I check if any procedure, view, trigger and function still exists. If so, I provoke an exception.
I know that this is kind of "dirty" solution (ignoring exceptions is usually a NoGo) but I have no better idea and as neither an endless loop nor undetected error can happen, I will use this way.
EXECUTE BLOCK
as
declare x integer;
declare y integer;
declare z integer;
declare s varchar(100);
declare s1 varchar(100);
begin
x=1;
y=0;
while (x>0 and y<100) do
-- we break out of the loop if we have more than 100 rounds as this indicates
-- at least one object could not be deleted.
begin
y=y+1;
for SELECT distinct RDB$VIEW_NAME from RDB$VIEW_RELATIONS into :s do
begin
in autonomous transaction do
execute statement 'alter view ' || s || ' as select 1 as test from rdb$database';
-- Ignore errors here for now
in autonomous transaction do
execute statement 'drop view ' || s;
-- Ignore errors here for now
when any do begin end
end
for SELECT RDB$PROCEDURE_NAME from RDB$PROCEDURES into :s do
begin
in autonomous transaction do
execute statement 'drop procedure ' || s;
-- Ignore errors here for now
when any do begin end
end
for select RDB$TRIGGER_NAME from RDB$TRIGGERS where RDB$SYSTEM_FLAG=0 into :s do
begin
in autonomous transaction do
execute statement 'drop trigger ' || s;
-- Ignore errors here for now
when any do begin end
end
for select RDB$FUNCTION_NAME from RDB$FUNCTIONS into :s do
begin
in autonomous transaction do
execute statement 'drop function ' || s;
-- Ignore errors here for now
when any do begin end
end
for select rdb$constraint_name,rdb$relation_name from RDB$RELATION_CONSTRAINTS where not rdb$relation_name containing ('$') into :s,:s1 do
begin
in autonomous transaction do
execute statement 'alter table ' || s1 || ' drop constraint ' || s;
-- Ignore errors here for now
when any do begin end
end
for select rdb$index_name from rdb$indices where rdb$system_flag=0 into :s do
begin
in autonomous transaction do
execute statement 'drop index ' || s;
-- Ignore errors here for now
when any do begin end
end
x = 0;
SELECT count(*) from RDB$PROCEDURES into :z;
x = x + z;
SELECT count(distinct RDB$VIEW_NAME) from RDB$VIEW_RELATIONS into :z;
x = x + z;
select count(*) from RDB$TRIGGERS where RDB$SYSTEM_FLAG=0 into :z;
x = x + z;
select count(*) from RDB$FUNCTIONS into :z;
x = x + z;
select count(*) from RDB$RELATION_CONSTRAINTS where not rdb$relation_name containing ('$') into :z;
x = x + z;
select count(*) from rdb$indices where rdb$system_flag=0 into :z;
x = x + z;
end
if (x>0) then
-- Raise an exception showing that the block failed
y=x/0;
end
UPDATE: I added code to drop all constraints and indexes.
UPDATE 2: It might be a good idea to preserve "not null" constraints as they are only recreatable with a domain. To do so just change the select statement for constraints to:
for select rdb$constraint_name,rdb$relation_name from RDB$RELATION_CONSTRAINTS
where rdb$constraint_type<>'NOT NULL' and not rdb$relation_name containing ('$') into :s,:s1 do
