1

I am creating a web app that handles coupons. When a user claims a coupon the coupon batch count "should" be updated, and a new coupon is created.

For some reason the UPDATE query does not work inside my PostgreSQL function. Everything else works fine.

I have a coupon batch:

CREATE TABLE public.batch (
    batch_id integer NOT NULL,
    template_id integer NOT NULL,
    created_count integer NOT NULL,
    claimed_count integer NOT NULL,
    redeemed_count integer NOT NULL,
    start_date date NOT NULL,
    expiry_date date NOT NULL,
    status_id integer NOT NULL
);

When a coupon is "claimed" from a batch a new record is created in coupons:

CREATE TABLE public.coupons (
    coupon_id integer NOT NULL,
    code character varying(32) NOT NULL,
    customer_id integer NOT NULL,
    batch_id integer NOT NULL,
    is_redeemed boolean NOT NULL,
    date_claimed date NOT NULL,
    date_used date,
    redeemed_discount integer,
    purchased_item character varying(30)
);

The postgres function to claim a coupon from a batch:

/*Claim a coupon */
CREATE OR REPLACE FUNCTION claim_from_batch(businessID INTEGER,batchID INTEGER, customerID INTEGER) 
    RETURNS INTEGER AS $$

DECLARE
    couponBatchID INTEGER;
    newCouponID INTEGER;
BEGIN
    PERFORM update_batch_status();

   /*Check if the batch is available to the customer*/
    SELECT INTO couponBatchID batches.batch_id 
    FROM get_customer_available_batches(customerID) as batches 
    WHERE batches.batch_id = batchID
    AND batches.business_id = businessID;

    IF couponBatchID IS NOT NULL THEN
        RAISE NOTICE 'success';
        /*update the coupons_claimed value*/
        UPDATE batch SET claimed_count =  cp.count 
        FROM (SELECT COUNT(coupon_id) FROM coupons WHERE batch_id = batchID) AS cp
        WHERE batch_id = batchID;

        /*Create the coupon*/
        INSERT INTO coupons(code,customer_id,batch_id,is_redeemed,date_claimed)
        VALUES('PLACEHOLDER', customerID,batchID,false,NOW())
        RETURNING coupons.coupon_id INTO newCouponID;
         IF newCouponID IS NOT NULL THEN
            RETURN newCouponID;
         END IF;
    ELSE
        RAISE NOTICE 'failed';
        RETURN -1;
    END IF;
END $$ LANGUAGE 'plpgsql';

The strangest thing for me is that when I run the update query with a hard-coded batch_id number, outside the function, in the query builder of pg-admin, the value gets updated correctly.

I tried multiple variations of this and and searched through the internet, I also tried separating the update to a separate function and call it inside the claim function. Nothing works.

I can't debug the reason why this is not working. I am still a CS student so excuse any other obvious mistakes I might have missed.

I tried changing the update portion of the function to:

UPDATE batch
 SET claimed_count = (SELECT COUNT(coupon_id) 
                      FROM coupons 
                      WHERE batch_id = batchID)
 WHERE batch_id = batchID;

P.S I am not sure if Updating the claimed_count value like this, is the best approach, since the value simply increases by one (1). But it made sense because if I later wanted to (possibly) return a coupon to the batch, I would need the value to be updated.

Thank you for your time.

5
  • This is hard to debug without access to your system or test data that exhibit the problem. Aug 21, 2019 at 11:44
  • Perhaps there is no such row in batches with that batchID. Aug 21, 2019 at 12:01
  • @ypercubeᵀᴹ There is though. I tried the query outside the function with correct values. And I called the function with correct values as well. Aug 21, 2019 at 12:04
  • Perhaps the IF ELSE is not designed properly? Does the INSERT INTO coupons happen? Aug 21, 2019 at 12:54
  • @ypercubeᵀᴹ Yes. Insert happens. the update does not work. Aug 21, 2019 at 13:47

1 Answer 1

0

Late to the party, but I recently had this same issue. It turns out the cause for me was the RLS update policy on the table I was trying to update.

You'll want to make sure the RLS policy allows the user executing the function to also update the table, or mark your function as security definer to bypass RLS.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.