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

• AdamKG: A few issues. First, you'll need to use EXECUTE 'DELETE FROM '|| quote_ident(tablename), as written your code will always be trying to work against a table called "tablename". 2nd, use foreach, not for. 3rd (and I don't know why this is required) use the curly-brace array-literal syntax, so '{materials,processes}::text[]. ChatGPT made the fixes, I just tested the result, so not posting as an answer since that's not permitted.
• AdamKG: Oh, apparently the ARRAY part gets treated as part of the for-each syntax, so to use the square-bracket form you need a 2nd ARRAY to serve as the array-literal start token, resulting in FOREACH tablename IN ARRAY ARRAY['materials', 'processes'].
• Sig: @AdamKG, thanks for the reply. Unfortunately, I can't make your suggestions work (see the update above).

Answer 1

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$;