Deviopath › PostgreSQL

Order Total With Tax

Hard · Functions

A SQL function wraps a query behind a name, so you can reuse it: CREATE FUNCTION name(params) RETURNS type LANGUAGE sql AS $$ SELECT ... $$;. The value of the last SELECT is what the function returns.

Create order_total(p_order_id integer, p_tax_rate numeric) RETURNS numeric. For every line of the order, take quantity * unit_price and remove discount_pct percent of it. Add the lines up, multiply by 1 + p_tax_rate (so 0.18 means 18% tax) and round to 2 decimals with ROUND(..., 2). An order with no lines returns 0.00.

Tip: give parameters a prefix like p_. Inside a SQL function, a column called order_id would win over a parameter with the same name.

Next: Shipping Cost Tiers