Início / user-315500

bellysavalas's questions

Martin Hope
bellysavalas
Asked: 2024-11-12 10:57:30 +0800 CST
  • 6

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