Why should you care?

PostgresVerse loyalty points calculation spans tiers and promos — one plpgsql function keeps logic near data.

See it live — copy this example

Run in pgAdmin or psql.

CREATE OR REPLACE FUNCTION apply_loyalty_points(p_customer_id bigint, p_amount numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
  pts numeric;
BEGIN
  IF p_amount >= 1000 THEN
    pts := floor(p_amount / 100) * 2;
  ELSE
    pts := floor(p_amount / 100);
  END IF;
  UPDATE customers SET loyalty_points = loyalty_points + pts
  WHERE customer_id = p_customer_id;
  RETURN pts;
END;
$$;

SELECT apply_loyalty_points(7, 1500);

What happened?

• DECLARE defines pts.
• IF doubles points for orders ≥1000.
• UPDATE adds to customer.
• RETURN sends points back to caller.

Practice next

1. Add loyalty_points column to customers.
2. Create function and SELECT apply_loyalty_points.
3. Wrap body in EXCEPTION WHEN OTHERS for logging pattern.
4. Rewrite with CASE instead of IF chain.
5. Add STRICT SELECT INTO to error when no row found.

Remember

plpgsql adds control flow to SQL. DECLARE, BEGIN, END structure blocks. Use FOR loop FOR rec IN SELECT for row iteration.

PostgresVerse rewards engine

Checkout calls apply_loyalty_points in same transaction as order insert.

Outcome: Points always match paid amount even if app server crashes mid-request.