1

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Flash
  • 11
  • 2

1 Answers1

2

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • `when any` might be dangerous by masking all errors, not only "table does not exist" kind, but all others too (FK viloations, user grants violations, transaction races, everything). But we don't know much about the actual task TS tries to solve, maybe it is ok – Arioch 'The Jul 14 '20 at 11:24
  • @Arioch'The Yes it is a 'risk', but unfortunately you can't catch the "Table unknown" error (`dsql_relation_err`), because the `when gdscode` handler triggers on the primary error code, not on a secondary error code. And the primary error code in such a situation is the very broad `dsql_error` ("Dynamic SQL Error"). – Mark Rotteveel Jul 14 '20 at 11:29