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
Last activity: Jul 26, 2025, 12:02 AM