Capstone — Migrating and Extending a MySQL Database in PostgreSQL

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  parent_id INT REFERENCES categories(id),
  tax_rate NUMERIC(4,3) NOT NULL DEFAULT 0.000
);

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  category_id INT REFERENCES categories(id),
  price NUMERIC(10,2) NOT NULL,
  attributes JSONB
);
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INT REFERENCES orders(id),
  product_id INT REFERENCES products(id),
  quantity INT NOT NULL,
  unit_price NUMERIC(10,2) NOT NULL
);

WITH RECURSIVE breadcrumb AS (
  SELECT id, name, parent_id, name::TEXT AS path
  FROM categories
  WHERE id = 15  -- e.g. "Laptops"

  UNION ALL

  SELECT c.id, c.name, c.parent_id, c.name || ' > ' || breadcrumb.path
  FROM categories c
  JOIN breadcrumb ON c.id = breadcrumb.parent_id
)
SELECT path FROM breadcrumb WHERE parent_id IS NULL;
-- 'Electronics > Computers > Laptops'

CREATE FUNCTION order_total_with_tax(order_id_param INT)
RETURNS NUMERIC AS $$
DECLARE
  subtotal NUMERIC := 0;
  tax_total NUMERIC := 0;
  item RECORD;
  cat_tax NUMERIC;
BEGIN
  FOR item IN
    SELECT oi.quantity, oi.unit_price, p.category_id
    FROM order_items oi
    JOIN products p ON p.id = oi.product_id
    WHERE oi.order_id = order_id_param
  LOOP
    subtotal := subtotal + (item.quantity * item.unit_price);

    WITH RECURSIVE cat_walk AS (
      SELECT id, parent_id, tax_rate FROM categories WHERE id = item.category_id
      UNION ALL
      SELECT c.id, c.parent_id, c.tax_rate
      FROM categories c
      JOIN cat_walk ON c.id = cat_walk.parent_id
    )
    SELECT tax_rate INTO cat_tax FROM cat_walk WHERE tax_rate > 0 LIMIT 1;

    tax_total := tax_total + (item.quantity * item.unit_price * COALESCE(cat_tax, 0));
  END LOOP;

  RETURN subtotal + tax_total;
END;
$$ LANGUAGE plpgsql;

SELECT order_total_with_tax(42);