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

I am trying to write a Postgres procedure in pgAdmin. The procedure I've written fails with different error messages depending on where I put the cursor. It even sometimes seems to succeed, but with no procedure created. My questions: 1. What is wrong with my code? 2. Is there a better/simpler way to achieve the same thing? The below code tries to iterate through a table of values (outer loop), and for each row in that table, to iterate through another table that has some missing values (inner loop). Whenever a missing value is found in the inner loop table, if a value that can be used exists in the outer table, I update the inner table with that value. ~~~pgsql CREATE OR REPLACE PROCEDURE impute() LANGUAGE plpgsql AS $$ DECLARE cntry_dec_means RECORD; table_row RECORD; BEGIN > FOR cntry_dec_means IN SELECT * FROM country_decade_means LOOP -- if all imputed values are null, don't do anything and move to the next row IF ISNULL(cntry_dec_means.gdppc) AND ISNULL(cntry_dec_means.gdp) AND ISNULL(cntry_dec_means.ed_gdp) AND ISNULL(cntry_dec_means.population) THEN CONTINUE; -- get matching rows from the actual table > FOR table_row IN SELECT * FROM inls625_impute WHERE country = cntry_dec_means.country AND decade = cntry_dec_means.decade LOOP IF (table_row.gdppc IS NULL) AND (cntry_dec_means.gdppc IS NOT NULL) THEN UPDATE inls625_impute SET gdppc = cntry_dec_means.gdppc WHERE inls625_impute.country = cntry_dec_means.country AND inls625_impute.decade = cntry_dec_means.decade; END LOOP; --> END LOOP; --> END; $$; ~~~ #### Update Thanks too @prasad for clarifying how to use loop labels. Unfortunately, it does not seem to work for me. Here are the errors (in psql): ~~~pgsql CREATE OR REPLACE PROCEDURE impute() LANGUAGE plpgsql AS $$" LINE 1: $$; ^ INLS-623-Labs=# table_row RECORD; ERROR: syntax error at or near "table_row" LINE 1: table_row RECORD; ^ INLS-623-Labs=# BEGIN INLS-623-Labs-# > INLS-623-Labs-# FOR cntry_dec_means IN INLS-623-Labs-# SELECT * FROM country_decade_means INLS-623-Labs-# LOOP INLS-623-Labs-# -- if all imputed values are null, don't do anything and move to the next row INLS-623-Labs-# IF ISNULL(cntry_dec_means.gdppc) INLS-623-Labs-# AND ISNULL(cntry_dec_means.gdp) INLS-623-Labs-# AND ISNULL(cntry_dec_means.ed_gdp) INLS-623-Labs-# AND ISNULL(cntry_dec_means.population) THEN INLS-623-Labs-# CONTINUE outer_loop; ERROR: syntax error at or near "> ^ INLS-623-Labs=# INLS-623-Labs=# -- get matching rows from the actual table INLS-623-Labs=# > INLS-623-Labs-# FOR table_row IN INLS-623-Labs-# SELECT * FROM inls625_impute INLS-623-Labs-# WHERE country = cntry_dec_means.country INLS-623-Labs-# AND decade = cntry_dec_means.decade INLS-623-Labs-# LOOP INLS-623-Labs-# IF ISNULL(table_row.gdppc) AND NOT ISNULL(cntry_dec_means.gdppc) THEN INLS-623-Labs-# UPDATE inls625_impute SET gdppc = cntry_dec_means.gdppc INLS-623-Labs-# WHERE inls625_impute.country = cntry_dec_means.country INLS-623-Labs-# AND inls625_impute.decade = cntry_dec_means.decade; ERROR: syntax error at or near "> ^ INLS-623-Labs=# END LOOP inner_loop; ERROR: syntax error at or near "LOOP" LINE 1: END LOOP inner_loop; ^ INLS-623-Labs=# END LOOP outer_loop; ERROR: syntax error at or near "LOOP" LINE 1: END LOOP outer_loop; ^ INLS-623-Labs=# END; WARNING: there is no transaction in progress COMMIT INLS-623-Labs=# $$; INLS-623-Labs$# ~~~ What's more, I reproduced the example provided by @prasad and get similar errors: ~~~pgsql CREATE OR REPLACE PROCEDURE t_proc() LANGUAGE plpgsql AS $$" LINE 1: $$; ^ INLS-623-Labs=# t2_rec RECORD; ERROR: syntax error at or near "t2_rec" LINE 1: t2_rec RECORD; ^ INLS-623-Labs=# BEGIN INLS-623-Labs-# > INLS-623-Labs-# FOR t_rec IN INLS-623-Labs-# SELECT * FROM t INLS-623-Labs-# LOOP INLS-623-Labs-# IF t_rec.name IS NULL AND t_rec.data IS NULL THEN INLS-623-Labs-# CONTINUE outer_loop; ERROR: syntax error at or near "> ^ INLS-623-Labs=# END IF; ERROR: syntax error at or near "IF" LINE 1: END IF; ^ INLS-623-Labs=# RAISE NOTICE 't.id value: %', t_rec.id; ERROR: syntax error at or near "RAISE" LINE 1: RAISE NOTICE 't.id value: %', t_rec.id; ^ INLS-623-Labs=# > INLS-623-Labs-# FOR t2_rec IN INLS-623-Labs-# SELECT * FROM t2 WHERE t2.id = t_rec.id INLS-623-Labs-# LOOP INLS-623-Labs-# RAISE NOTICE 't2.id value: %', t2_rec.id; ERROR: syntax error at or near "> ^ INLS-623-Labs=# END LOOP inner_loop; ERROR: syntax error at or near "LOOP" LINE 1: END LOOP inner_loop; ^ INLS-623-Labs=# END LOOP outer_loop; ERROR: syntax error at or near "LOOP" LINE 1: END LOOP outer_loop; ^ INLS-623-Labs=# END; WARNING: there is no transaction in progress COMMIT INLS-623-Labs=# $$; ~~~

I have the following execution plan: As you can see, the row estimates for the Clustered Index Scan and Index Seek operators are accurate. However, the Nested Loops join has a significant discrepancy: the actual row count is 6,420, while the estimated row count is only 72. My questions are: 1. How is the row count estimated for a Nested Loops join in SQL Server? 2. What factors could lead to such an inaccurate row estimate in this case? 3. Is there anything I can do to improve or correct the estimate? Thank you for any insights!

Long story short I need to replace a website component with a newer one. Problem is the way the old one stored information in the database is very convoluted. I have a new empty table (for the new component) with 3 columns: | field_id | item_id | value | Each value has a different item_id but field_id is a static number (2). I can populate the table from the old component's table (where x is the data I am unable to get from the old table):

INSERT INTO new_table (field_id, item_id, value)
    SELECT 2, x , content FROM old_table WHERE condition;

The problem is item_id (x above), the old component has this value stored in a different table but the only thing linking the two values is an asset_id from ANOTHER table. So for each row I somehow need to do the following: 1. Get a value called asset_id from old_table_form, 2. Then in a table old_table_content I need the value item-id from the same row as asset_id, 3. Save this value in new_table as item_id for each row. I thought maybe nesting SELECT commands could work but I am having trouble even visualizing how it should be, or maybe a loop with pseudo-code something like:

LOOP
    DECLARE var1
    DECLARE var2
    SELECT FROM old_table_form -> 'asset_id'
    var1 = asset_id
    SELECT FROM old_table_content -> 'item_id' from 'asset_id' row
    var2 = 'item_id'
    save var2 in new table as 'item_id'
    repeat for all rows in table
    END LOOP

Is something like this even possible in MySQL/phpmyadmin? Any ideas/advice would be greatly appreciated.

There are two tables C and B. They're connected by C.id=B.cid. I need to find all the C.id es where: - None of the rows corresponding to cid has is_true='Y' I've tried various queries but can't wrap my head around any of them. This is one of my ideas. select * from C inner join B on C.id=B.cid where -- loop all rows of B using B.cid -- output if the row has is_true!='Y'