using execute in postgres returns syntax error

Question

I'm trying to debug this adn find out why I'm getting syntax error:

CREATE OR REPLACE FUNCTION public.myfunc(_report_id integer, _cutoff_date date)
 RETURNS record
 LANGUAGE plpgsql
AS $function$
declare
    _deliverable_id RECORD  ;
BEGIN
FOR _deliverable_id IN
      SELECT deliverable_id FROM public.deliverables where report_id=_report_id
   LOOP
      execute format('DROP TABLE IF EXISTS report.products_%I',_deliverable_id);
   END LOOP;
    
END
$function$
;

When I execute this, I get:

syntax error at or near ""(1111)""

1111 is one deliverable for sure, so this leads me to think it has something to do with the execute statement format, or the way I'm using %I?

Answer 1

%I is replaced as a whole identifier. If you want to concatenate things, you need to do it before replacement.

You can test/debug this for yourself by inspecting the result of the format() function:

select format('DROP TABLE IF EXISTS report.products_%I',42);

returns DROP TABLE IF EXISTS report.products_"42"

you need to use:

select format('DROP TABLE IF EXISTS report.%I',concat('products_', 42));

which correctly returns DROP TABLE IF EXISTS report.products_42

(obviously you need to replace 42 with your variable.