How to skip/continue delete statement when the table does not exist?

Question

I want to write execute block that I can use for multiple databases with similar structure, some don't have 1 or more tables. I use something like this

 execute block as
 begin
 delete from table1;
 delete from table2;
 delete from table3;
 delete from table4;
 delete from table5;
 end

and this works for base with all tables, but when table is missing the execute block stops. I want to use this execute block when there is missing table so I don't have to commit the missing one.

Answer 1

You can't do it like this. A table referenced in a delete PSQL statement must exist for the execute block to successfully compile before it can even run.

Instead you will need to use statement blocks containing a execute statement to execute the statements dynamically and a when any exception handler to continue on any error.

For example, something like:

execute block as
begin
  begin
    execute statement 'delete from table1';
    when any do
    begin
      -- ignore error
    end
  end
  begin
    execute statement 'delete from table2';
    when any do
    begin
      -- ignore error
    end
  end
  -- etc
end

You could also check for existence in the system tables before executing the delete dynamically. For example, something like:

execute block
as
declare variable tablename varchar(64);
begin
  for select trim(rdb$relation_name) 
      from rdb$relations 
      where rdb$relation_name in ('TABLE1', 'TABLE2', 'TABLE3', 'TABLE4', 'TABLE5')
      into tablename do
  begin
    execute statement 'delete from "' || replace(tablename, '"', '""') || '"';
  end
end

The table names in the IN-clause must match the name as stored (for unquoted object names like table1 that means uppercase TABLE1). The replace(tablename, '"', '""') is just for completeness to escape possible double quotes in table names.