PL/pgSQL lets PostgreSQL functions combine SQL with variables, conditionals, data changes, and returned query results. A practical use case is managing a customer’s shopping cart inside the database. section 2.6.1
The shopping-cart function follows this flow:
- Accept customer, product, and quantity arguments, then return the pending order’s items. section 2.6.1
- Find the customer’s pending order, create one when absent, and capture its generated UUID with RETURNING. section 2.6.1
- Use MERGE to add a product or update its quantity, then return the complete cart with RETURN QUERY. section 2.6.1
Adding products to a cart
Calling sales.order_add_item(customer_id_param => 3, product_id_param => 3, quantity_param => 2) returns one cart row with quantity 2 and price 13.99. Adding product 5 returns both cart items, while the customer still has one pending order.
