Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

1.1K
Visualizações
postgres SQL state: P0001 error in when running function

Postgres DB

If I run below function using query SELECT sms.somefunc();, got error SQL state: P0001.

I called another function within this function.

Can someone help me to sort out this issue? I don't know why this error occur.

Thanks in advance.

--DROP FUNCTION SMS.somefunc();

CREATE FUNCTION SMS.somefunc() RETURNS void AS $BODY$

DECLARE

    wk_rows INT :=0;
    work_plan_id_val INT :=0;
    DECLARE wp_rows     INT DEFAULT 0;
BEGIN

    DECLARE wk_plan_id CURSOR FOR 

        SELECT COUNT(work_plan_id) FROM sms.work_plan 
            WHERE class_general_id = 8 AND
            subject_general_id = 18;

        BEGIN
            OPEN wk_plan_id;
                FETCH wk_plan_id INTO wk_rows;
            CLOSE wk_plan_id;
        END;

    DECLARE wp CURSOR FOR 

        SELECT work_plan_id FROM sms.work_plan 
            WHERE class_general_id = 8 AND
            subject_general_id = 18;  --442

        BEGIN
            OPEN wp;
            IF wk_rows > 0 THEN
                wp_rows = 0;
                WHILE wp_rows < wk_rows LOOP
                    FETCH wp INTO work_plan_id_val;

                    RAISE NOTICE 'work_plan_id_val %', work_plan_id_val;
                    EXECUTE SMS.P_DELETE_X (work_plan_id_val,'admin');
                    RAISE EXCEPTION 'Something happen %', work_plan_id_val;

                    wp_rows = wp_rows + 1;
                    --DBMS_OUTPUT.PUT_LINE('TEST');
                END LOOP;
            END IF;
            CLOSE wp;
        END;

END;

$BODY$ 
LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION SMS.somefunc()
  OWNER TO postgres;
over 4 years ago · Santiago Trujillo
1 Respostas
Responde à pergunta

0

Probably you would to use statement PERFORM not EXECUTE.

EXECUTE SMS.P_DELETE_X (work_plan_id_val,'admin');

should be

PERFORM SMS.P_DELETE_X (work_plan_id_val,'admin');

The EXECUTE statement should be used for dynamic SQL, not for function execution.

There is another smaller issue - you should to use EXISTS instead count(*) - and another issue - it is classic example of slow ISAM style.

over 4 years ago · Santiago Trujillo Relatório
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda