Início / dba / Perguntas / 343540
Accepted
bellysavalas
Asked: 2024-11-12 10:57:30 +0800 CST

Procedimento com loop FOR aninhado para atualizar linhas

  • 772

Estou tentando escrever um procedimento Postgres no pgAdmin. O procedimento que escrevi falha com diferentes mensagens de erro dependendo de onde coloco o cursor. Às vezes, parece até ter sucesso, mas sem nenhum procedimento criado.

Minhas perguntas:

  1. O que há de errado com meu código?
  2. Existe uma maneira melhor/mais simples de conseguir a mesma coisa?

O código abaixo tenta iterar por uma tabela de valores (loop externo) e, para cada linha dessa tabela, iterar por outra tabela que tenha alguns valores ausentes (loop interno). Sempre que um valor ausente for encontrado na tabela do loop interno, se um valor que pode ser usado existir na tabela externa, eu atualizo a tabela interna com esse valor.

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

Atualizar

Obrigado também @prasad por esclarecer como usar rótulos de loop. Infelizmente, não parece funcionar para mim.

Aqui estão os erros (no 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$#

Além disso, reproduzi o exemplo fornecido por @prasad e obtive erros semelhantes:

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 respostas

  1. Best Answer
    1. O que há de errado com meu código?

    Tecnicamente, só END IF;falta duas vezes.

    1. Existe uma maneira melhor/mais simples de conseguir a mesma coisa?

    Sim.UPDATE Uma declaração simples .

    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;

    Não só é muito mais simples e rápido, como também está correto . Seu procedimento (mesmo com sintaxe fixa) estaria aberto a condições de corrida . Entre a leitura de dados e a chegada ao UPDATEeventualmente, transações simultâneas poderiam interferir. Você poderia evitar isso com bloqueio explícito. Mas não vá por aí.

    Observe, em particular, esta formulação:

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

    Você só deseja pular a atualização se todas essas colunas forem nulas. (Além disso, c.gdppcnão podem ser nulas em nenhum caso.) Veja:

    • 2

  2. Aqui está como você pode codificar seu procedimento. Estou usando duas tabelas te t2para mostrar a maneira correta da sintaxe (LOOP, IF-END IF, etc.) que você está tentando em seu código.

    Estou usando o PostgreSQL versão 14 e os comandos são executados pelo psqlshell.

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

    A tabela ttem três colunas e os valores vazios acima são nullvalores. A tabela t2tem apenas uma coluna.

    TABLE t2;
 id
----
  2
  9

    O procedimento:

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

    Execute o procedimento e revise a saída:

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

    Referência adicional: https://www.postgresql.org/docs/14/plpgsql-control-structures.html

    • 0

relate perguntas