Sample Header Ad - 728x90

PostgreSQL loop through selected tables

0 votes
1 answer
156 views
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
Asked by Sig (455 rep)
Feb 13, 2025, 12:34 PM
Last activity: Jul 26, 2025, 12:02 AM