Try It Yourself - SyntaxStudy Editor

HTML / CSS / JS

-- Table-returning function
CREATE OR REPLACE FUNCTION get_top_customers(
  n INTEGER DEFAULT 10,
  from_date DATE DEFAULT (NOW() - INTERVAL '30 days')::DATE
)
RETURNS TABLE (
  customer_name TEXT,
  email         TEXT,
  order_count   BIGINT,
  total_spend   NUMERIC
) AS $$
  SELECT u.name, u.email, COUNT(o.id), COALESCE(SUM(o.total), 0)
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.id AND o.created_at >= from_date
  GROUP BY u.id, u.name, u.email
  ORDER BY total_spend DESC
  LIMIT n;
$$ LANGUAGE sql STABLE;

-- Call like a table
SELECT * FROM get_top_customers(5, '2024-01-01');

-- Standard view
CREATE OR REPLACE VIEW active_orders AS
  SELECT o.id, u.name AS customer, o.total, o.status, o.created_at
  FROM orders o
  JOIN users u ON u.id = o.user_id
  WHERE o.status NOT IN ('cancelled', 'refunded');

SELECT * FROM active_orders WHERE total > 100;

-- Materialised view — pre-computed monthly summary
CREATE MATERIALIZED VIEW monthly_revenue AS
  SELECT DATE_TRUNC('month', created_at) AS month,
         COUNT(*) AS orders, SUM(total) AS revenue
  FROM orders WHERE status = 'completed'
  GROUP BY 1;

CREATE UNIQUE INDEX ON monthly_revenue (month);

-- Refresh without locking reads
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;