PostgreSQL loop through selected tables

Question

In PG 17, I need to run some maintenance on several tables (in the code below, I have listed only two), So after some research, I came up with the following script:

DO
$do$
BEGIN 
   FOR tablename IN ARRAY['materials', 'processes'] LOOP
     DELETE FROM tablename;
     -- perform more operations ...
   END LOOP;
END
$do$;

But unfortunately, I get

Query 1 ERROR at Line 4: : ERROR: syntax error at or near "ARRAY" LINE 4: FOR tablename IN ARRAY['materials'] LOOP

What am I missing here?

UPDATE 1

Based on AdamKG comments, I updated my script as below:

DO
$do$
BEGIN 
   FOREACH tablename IN ARRAY ARRAY['materials'] LOOP
     EXECUTE 'DELETE FROM '|| quote_ident(tablename);  
   END LOOP;
END
$do$;

But now I get

Query 1 ERROR at Line 4: : ERROR: loop variable of FOREACH must be a known variable or list of variables LINE 4: FOREACH tablename IN ARRAY ARRAY['materials'] LOOP

DO
$do$
BEGIN 
   FOREACH tablename IN '{materials,processes}'::text[] LOOP
     EXECUTE 'DELETE FROM '|| quote_ident(tablename);  
   END LOOP;
END
$do$;

Generates

Query 1 ERROR at Line 4: : ERROR: syntax error at or near "'{materials}'" LINE 4: FOREACH tablename IN '{materials}'::text[] LOOP

score 0 · Answer 1 · answered Feb 24 '25 at 12:47

The code in UPDATE 1 is correct, except that you need to declare tablename (there is no auto-declaration of variables with plpgsql), as indicated by the error message:

Query 1 ERROR at Line 4: : ERROR: loop variable of FOREACH must be a known variable or list of variables LINE 4: FOREACH tablename IN ARRAY ARRAY['materials']

With the DECLARE statement added, the following code would work:

DO
$do$
DECLARE
  tablename text;
BEGIN 
   FOREACH tablename IN ARRAY ARRAY['materials'] LOOP
     EXECUTE 'DELETE FROM '|| quote_ident(tablename);  
   END LOOP;
END
$do$;

It may be worth mentioning that the array construct is not necessary. The more genereric FOR loop takes values from a query and that query can consist of simply a VALUES clause. The following code block would work as well:

DO
$do$
DECLARE
  tablename text;
BEGIN 
   FOR tablename IN VALUES('materials'),('other table') LOOP
     EXECUTE 'DELETE FROM '|| quote_ident(tablename);
   END LOOP;
END
$do$;

PostgreSQL loop through selected tables

1 Answers1