CODEWITHSUNDEEP
postgresqlplpgsqlpostgresql-9.2
Incerteza
Incerteza

Reputation: 34884

Update the records if they exist

Periodically I need to check the records in a table and update them, in particular check the records with "payment_pending" status and update them to "payment_pending_expired" status. But I'm not sure how to do it properly:

CREATE OR REPLACE FUNCTION cancel_pending_orders(user_id integer, max_day_amount_allowed integer)
  RETURNS SETOF void AS
$BODY$

BEGIN
  -- if the records exist at all...
  IF EXISTS (
    SELECT ph.id FROM payment_history as ph  
    INNER JOIN payment AS p
    ON p.id = ph.payment_id
    WHERE p.user_id = $1
    AND ph.status = 'payment_pending'
    AND ph.added_at + max_day_amount_allowed <= now()
  ) THEN
    -- make all of them status = 'payment_pending_expired'


    RETURN;
  END IF;

The questions: 1) How do I add max_day_amount_allowed to ph.added_at? If it were a literal I could do this by:

....

  AND (ph.added_at + interval '30d') <= now()

but it is not a literal, it is a variable.

2) How do I refer to the found records (in case, the exist) 

....
  ) THEN
    -- make all of them ph.status = 'payment_pending_expired'
    -- but how do I refer to them?

    RETURN;

P.S. ph.status has a type of varchar and not integer only for simplicity.

Upvotes: 0

Views: 67

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656321

How do I add max_day_amount_allowed to ph.added_at?

Assuming the type timestamp for added_at.

Don't convert to text, concatenate and convert back. Just multiply an interval:

ph.added_at + interval '1d' * max_day_amount_allowed <= now()

Or, if added_at is a date, you can just add integer to a date. The date is then coerced to timestamp automatically (according to local time) for the comparison to now():

ph.added_at + max_day_amount_allowed <= now()

Upvotes: 1

pozs
pozs

Reputation: 36214

1) You need to cast the day count to an interval: 

AND (ph.added_at + ($2 || ' days')::interval) <= now()

2) You can use CURSORs to do something with each row in a result-set.

But in your case (if you want to only update them) just use a single UPDATE command:

UPDATE payment_history AS ph
   SET ph.status = 'payment_pending_expired'
  FROM payment AS p
 WHERE p.id = ph.payment_id
   AND p.user_id = $1
   AND ph.status = 'payment_pending'
   AND (ph.added_at + ($2 || ' days')::interval) <= now()

Upvotes: 2

Related Questions