Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

1.1K
Vistas
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 Respuestas
Responde la pregunta

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 Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda