主页 / dba / 问题 / 343540
Accepted
bellysavalas
Asked: 2024-11-12 10:57:30 +0800 CST

使用嵌套 FOR 循环来更新行的过程

  • 772

我正在尝试在 pgAdmin 中编写 Postgres 过程。我编写的过程失败，并显示不同的错误消息，具体取决于我将光标放在哪里。有时它甚至似乎成功了，但没有创建任何过程。

我的问题：

  1. 我的代码有什么问题？
  2. 有没有更好/更简单的方法来实现同样的目标？

下面的代码尝试遍历一个值表（外循环），并针对该表中的每一行，遍历另一个具有一些缺失值的表（内循环）。每当在内循环表中发现缺失值时，如果外表中存在可用的值，我会用该值更新内表。

CREATE OR REPLACE PROCEDURE impute() 
LANGUAGE plpgsql 
AS $$ 
DECLARE 
    cntry_dec_means RECORD; 
    table_row RECORD; 
BEGIN 
    <<outer_loop>> 
    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 
        <<inner_loop>> 
        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; --<<inner_loop>> 
    END LOOP; --<<outer_loop>>
END; 
$$;

更新

也感谢@prasad 阐明了如何使用循环标签。不幸的是，它似乎对我不起作用。

以下是错误（在 psql 中）：

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-#     <<outer_loop>>
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 "<<"
LINE 2:     <<outer_loop>>
            ^
INLS-623-Labs=#
INLS-623-Labs=#         -- get matching rows from the actual table
INLS-623-Labs=#         <<inner_loop>>
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 "<<"
LINE 1: <<inner_loop>>
        ^
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$#

此外，我复现了@prasad 提供的示例并得到了类似的错误：

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-#     <<outer_loop>>
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 "<<"
LINE 2:     <<outer_loop>>
            ^
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=#         <<inner_loop>>
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 "<<"
LINE 1: <<inner_loop>>
        ^
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=# $$;
postgresql

2 个回答

  1. Best Answer
    1. 我的代码有什么问题？

    从技术上来说，仅END IF;失踪了两次。

    1. 有没有更好/更简单的方法来实现同样的目标？

    是的。简单的UPDATE声明。

    UPDATE inls625_impute i
SET    gdppc = c.gdppc 
FROM   cntry_dec_means c
WHERE  i.gdppc IS NULL
AND    c.gdppc IS NOT NULL
AND    ((c.gdp, c.ed_gdp, c.population) IS NULL) IS NOT TRUE
AND    i.country = c.country 
AND    i.decade = c.decade;

    它不仅更简单、更快速，而且是正确的。您的程序（即使使用固定语法）也容易受到竞争条件的影响。在读取数据和最终到达之间UPDATE，并发事务可能会干扰。您可以使用显式锁定来防止这种情况。但不要这样做。

    请特别注意这个表述：

    ((c.gdp, c.ed_gdp, c.population) IS NULL) IS NOT TRUE

    如果所有这些列都为空，则您只想跳过更新。（另外，c.gdppc在任何情况下都不能为空。）参见：

    • 2

  2. 以下是如何编写程序代码。我使用两个表格t来t2展示您在代码中尝试的语法（LOOP、IF-END IF 等）的正确方法。

    我正在使用 PostgreSQL 版本 14，并且命令从psqlshell 运行。

    TABLE t;
 id | name  | data
----+-------+------
  1 | first |   12
  2 |       |    9
  3 |       |

    表格t有三列，上面的空值是null值。表格t2只有一列。

    TABLE t2;
 id
----
  2
  9

    程序：

    CREATE OR REPLACE PROCEDURE t_proc() 
LANGUAGE plpgsql 
AS $$ 
DECLARE 
    t_rec RECORD;
    t2_rec RECORD; 
BEGIN 
    <<outer_loop>> 
    FOR t_rec IN 
        SELECT * FROM t 
    LOOP 
        IF t_rec.name IS NULL AND t_rec.data IS NULL THEN 
            CONTINUE outer_loop;
        END IF; 
        RAISE NOTICE 't.id value: %', t_rec.id;
        <<inner_loop>>
        FOR t2_rec IN
            SELECT * FROM t2 WHERE t2.id = t_rec.id
        LOOP
            RAISE NOTICE 't2.id value: %', t2_rec.id;
        END LOOP inner_loop; 
    END LOOP outer_loop;
END; 
$$;

    运行该过程并检查输出：

    CALL t_proc();
NOTICE:  t.id value: 1
NOTICE:  t.id value: 2
NOTICE:  t2.id value: 2
CALL

    更多参考： https://www.postgresql.org/docs/14/plpgsql-control-structures.html

    • 0

相关问题