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