Table-Returning Functions and Views

Functions that return SETOF a type or use TABLE(...) in their return type specification act as virtual tables and can be queried in the FROM clause just like a regular table. This allows you to encapsulate parameterised queries as callable functions, which is useful for report generation and reusable data access patterns. Views are stored queries that behave like tables — simple views on a single table are automatically updatable in PostgreSQL. Materialised views store the query result physically on disk, making complex aggregate queries much faster to read at the cost of staleness until you run REFRESH MATERIALIZED VIEW. The CONCURRENTLY option refreshes a materialised view without taking an exclusive lock, allowing reads during the refresh.

Example

-- 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;

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned