Deviopath › PostgreSQL

Shipping Cost Tiers

Hard · Functions

PL/pgSQL adds variables and control flow to functions. Inside BEGIN ... END, you can write IF cond THEN ... ELSIF cond THEN ... ELSE ... END IF; and RETURN value;.

Create shipping_cost(p_order_total numeric, p_weight_kg numeric) RETURNS numeric with LANGUAGE plpgsql. Check the rules from top to bottom; the first one that matches wins:

1. Order total of 100 or more: 0.00 (free shipping). 2. Weight up to 1 kg: 4.99. 3. Weight up to 5 kg: 9.99. 4. Heavier: 9.99 plus 2.00 for every started kg above 5 (use CEIL). So 7.10 kg costs 9.99 + 3 * 2.00 = 15.99.

Next: Generated Line Total