Views
CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE deleted_at IS NULL;Materialized Views
CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', created_at) AS day,
SUM(total) AS revenue
FROM orders
GROUP BY 1;
REFRESH MATERIALIZED VIEW daily_sales;Functions
CREATE FUNCTION total_orders(user_id INT)
RETURNS INT AS $$
SELECT COUNT(*) FROM orders WHERE orders.user_id = $1;
$$ LANGUAGE SQL;Popular Extensions
- • uuid-ossp: UUID generation
- • pg_trgm: similarity search
- • postgis: geospatial queries