Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    57

    Unanswered: ERROR: canceling query due to user request

    ok, here is what's going on: i have 2 database functions running on PostgreSQL 7.4.8 on i386-red hat-linux-guru (Red Hat 3.4.3-22). Here are the functions:

    CREATE OR REPLACE FUNCTION public.coursepaymentdistribution0_function(int4)
    RETURNS int4 AS
    'DECLARE
    argsCourseRecordPaymentID ALIAS FOR $1;
    varAccountID Integer;
    varChargeTypeID Integer;
    varCourseChargeChargeID Integer;
    varCourseChargeChargePaymentID Integer;
    varCourseInstanceID Integer;
    varCourseRecordID Integer;
    varCourseTypeID Integer;
    varAmountPaid Float;
    varPaymentAmount Float;
    varBookChargeFound Boolean;
    varCourseCharge RECORD;
    varCourseChargeCharge RECORD;
    BEGIN
    varAccountID = -1;
    varAmountPaid = 0;
    varBookChargeFound = False;
    varChargeTypeID = -1;
    varCourseChargeChargeID = -1;
    varCourseChargeChargePaymentID = -1;
    varCourseInstanceID = -1;
    varCourseRecordID = -1;
    varCourseTypeID = -1;
    varPaymentAmount = 0;

    SELECT CourseRecordID INTO varCourseRecordID
    FROM COURSERECORDPAYMENT
    WHERE CourseRecordPaymentID = argsCourseRecordPaymentID;

    SELECT Amount INTO varPaymentAmount
    FROM COURSERECORDPAYMENT
    WHERE CourseRecordPaymentID = argsCourseRecordPaymentID;

    IF (varCourseRecordID > 0 AND varPaymentAmount > 0) THEN
    SELECT
    PERSONACCOUNT.AccountID INTO varAccountID
    FROM
    COURSERECORD,
    PERSONACCOUNT,
    STUDENT
    WHERE
    COURSERECORD.CourseRecordID = varCourseRecordID
    AND PERSONACCOUNT.PersonalInformationID = STUDENT.PersonalInformationID
    AND STUDENT.StudentID = COURSERECORD.StudentID
    LIMIT 1;

    SELECT CourseInstanceID INTO varCourseInstanceID
    FROM COURSERECORD
    WHERE CourseRecordID = varCourseRecordID;

    IF (varAccountID > 0 AND varCourseInstanceID > 0) THEN
    FOR varCourseCharge IN SELECT * FROM COURSECHARGE WHERE CourseInstanceID = varCourseInstanceID ORDER BY CASE ChargeTypeID When 14 Then 1 When 13 Then 2 End DESC LOOP

    IF (varCourseCharge.CourseChargeID > 0 AND varPaymentAmount > 0) THEN

    FOR varCourseChargeCharge IN SELECT * FROM COURSECHARGECHARGE WHERE AccountID = varAccountID AND CourseChargeID = varCourseCharge.CourseChargeID LOOP

    IF (varCourseChargeCharge.CourseChargeChargeID > 0 AND varPaymentAmount > 0) THEN
    IF (varCourseChargeCharge.AmountDue > 0 AND varCourseChargeCharge.ChargeStatusID <> 2) THEN
    IF varPaymentAmount >= varCourseChargeCharge.AmountDue THEN

    EXECUTE \'UPDATE coursechargecharge SET chargestatusID = 2, totalamountpaid = \' || varCourseChargeCharge.AmountDue || \' WHERE coursechargechargeid = \' || varCourseChargeCharge.CourseChargeChargeID;
    EXECUTE \'INSERT INTO coursechargechargepayment(coursechargechargeid, courserecordpaymentid, amount) VALUES(\' || varCourseChargeCharge.CourseChargeChargeID || \', \' || argsCourseRecordPaymentID || \', \' || varCourseChargeCharge.AmountDue || \')\';

    varPaymentAmount = varPaymentAmount - varCourseChargeCharge.AmountDue;
    ELSIF varPaymentAmount > 0 THEN

    EXECUTE \'UPDATE coursechargecharge SET totalamountpaid = \' || varPaymentAmount || \' WHERE coursechargechargeid = \' || varCourseChargeCharge.CourseChargeChargeID;
    EXECUTE \'INSERT INTO coursechargechargepayment(coursechargechargeid, courserecordpaymentid, amount) VALUES(\' || varCourseChargeCharge.CourseChargeChargeID || \', \' || argsCourseRecordPaymentID || \', \' || varPaymentAmount || \')\';

    varPaymentAmount = 0;
    END IF;
    END IF;
    END IF;
    END LOOP;
    END IF;
    END LOOP;

    ...........................
    ...........................


    END IF;
    END IF;
    END IF;
    END IF;

    RETURN 1;
    END;'
    LANGUAGE 'plpgsql' VOLATILE;

    And the other one is:

    CREATE OR REPLACE FUNCTION public.coursepaymentdistribution2_function(int4, int4)
    RETURNS int4 AS
    'DECLARE
    argsLower ALIAS FOR $1;
    argsUpper ALIAS FOR $2;
    varCourseRecordPayment RECORD;
    BEGIN
    IF (argsLower > -1 AND argsUpper > 0) THEN
    FOR varCourseRecordPayment IN SELECT * FROM COURSERECORDPAYMENT WHERE CourseRecordPaymentID > argsLower AND CourseRecordPaymentID < argsUpper LOOP
    IF varCourseRecordPayment.CourseRecordPaymentID > 0 THEN
    EXECUTE \'SELECT public.coursepaymentdistribution0_function(\' || varCourseRecordPayment.CourseRecordPaymentID || \')\';
    END IF;
    END LOOP;
    END IF;

    RETURN 1;
    END;'
    LANGUAGE 'plpgsql' VOLATILE;

    I run the functions using psql via CygWin like so:

    select public.coursepaymentdistribution2_function(40, 101);

    And I get the following error:

    ERROR: canceling query due to user request
    CONTEXT: PL/pgSQL function "coursepaymentdistribution0_function" line 57 at for
    over select rows
    PL/pgSQL function "coursepaymentdistribution2_function" line 9 at execute statem
    ent

    Through some experimentation, namely using the RAISE NOTICE keyword to print the values of different variables, I have found out the error occurs on the following line of function coursepaymentdistribution0_function:

    FOR varCourseChargeCharge IN SELECT * FROM COURSECHARGECHARGE WHERE AccountID = varAccountID AND CourseChargeID = varCourseCharge.CourseChargeID LOOP

    Anybody know how to fix that?

    Thanks.

  2. #2
    Join Date
    Feb 2005
    Location
    Colorado Springs
    Posts
    222
    I'm not entirely sure, but have a couple of ideas. After your LOOP, there is the statement

    EXECUTE \'SELECT public.coursepaymentdistribution0_function(\' || varCourseRecordPayment.CourseRecordPaymentID || \')\';

    CourseRecordPaymentID is cast as int4 and you may need to explicitly obtain the string equivalent to concatenate it. Second, I didn't go through all your variables but note your code contains a lot of VariableNames. PostgreSQL is pretty picky about case and consistently converts them to lower case; perhaps there is a problem with some of your variable names. I know I used to use VariableName a lot and had to train myself to use variable_name with PostgreSQL.

    Sorry if this is not much help, but the syntax in the LOOP statement looks fine otherwise.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •