Supabase RPC function not updating table data as expected – Reactjs

I have created a Supabase function to update the expiration_date and status of a ‘listing’ in the ‘listings’ table after a successful payment. I decided to create a function because I wanted to set the expiration_date based on the server time (I am not sure if there is any other way to retrieve the server time via client side). The function is running without errors, but the data in the database is not being updated. I have also tried debugging the issue by modifying the function to return the number of rows affected by the update query, but the result is always 0, even though the listing_id passed to the function is valid and exists in the listings table.

Here is what I did:

1. Created a PostgreSQL function in Supabase:

CREATE OR REPLACE FUNCTION update_listing_after_payment_success(listing_id BIGINT)
RETURNS BIGINT AS $$
BEGIN
  UPDATE listings
  SET expiration_date = NOW() + INTERVAL '30 days', status = 'active'
  WHERE id = listing_id;

  RETURN listing_id;
END;
$$ LANGUAGE plpgsql;

2. Called the function from a Next.js application using the Supabase client:

const updateListingAfterPayment = async (id) => {
  try {
    const result = await supabase.rpc("update_listing_after_payment_success", {
      listing_id: id,
    });

    if (result.error) {
      console.error("Error updating listing after payment:", result.error.message);
    } else {
      console.log("Returned listing_id:", result.data);
    }
  } catch (error) {
    console.error("Unexpected error:", error.message);
  }
};

updateListingAfterPayment(listingId);

This update query works correctly when executed directly in the Supabase SQL editor.

The expiration_date and status of the listing with the specified listing_id should be updated in the listings table, but it’s not happening. The data in the listings table is not being updated, even though the function runs without errors and the listing_id is valid and exists in the table.

The listings table schema includes the following columns: id (bigint), expiration_date (timestamp), and status (varchar).

I would greatly appreciate any help or suggestions on how to solve this issue. Thanks in advance!