Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

1.1K
Views
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 answers
Answer question

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 Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!