postgresql - 更新记录(如果存在)

Question

我需要定期检查表中的记录并更新它们，特别是检查状态为“payment_pending”的记录并将其更新为“payment_pending_expired”状态。但我不确定如何正确地做到这一点:

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;

问题:1) 如何将 max_day_amount_allowed 添加到 ph.added_at？如果它是文字，我可以通过以下方式做到这一点:

....

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

但它不是文字，它是一个变量。

2) 如何引用找到的记录(如果存在)

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

    RETURN;

附言ph.status 的类型是 varchar 而不是 integer 只是为了简单。

Answer 1

1) 你需要cast一天计数到一个间隔:

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

2) 您可以使用 CURSOR s 对结果集中的每一行做一些事情。

但在您的情况下(如果您只想更新它们)只需使用一个 UPDATE 命令:

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()