SQL Libraries: Define Once, Compose

Define Once, Compose

A team could ask the same question a hundred times. Alice calls a user active if they listened this week, Bob counts the last thirty days, and an LLM drafting the query picks a third rule: three dashboards, three definitions of "active user." The fix is the one software uses for any repeated code, a library. Write each definition once as a named, parameterized query (in Postgres, a table-valued function) and call it by name.

-- definitions.sql: each term is a named function you compose by name.

-- Step 1: Listener of an artist
CREATE FUNCTION listener_of(artist TEXT) RETURNS TABLE(user_id INT) AS $$
  SELECT DISTINCT l.user_id
  FROM Listens l JOIN Songs s ON l.song_id = s.song_id
  WHERE s.artist = listener_of.artist          -- the parameter, qualified
$$ LANGUAGE sql;

-- Step 2: Active in a recent window
CREATE FUNCTION active_since(days INT) RETURNS TABLE(user_id INT) AS $$
  -- a day follows the database session's calendar boundary, not a rolling 24h;
  -- a NULL listen_time never counts as active.
  SELECT DISTINCT user_id FROM Listens
  WHERE listen_time >= CURRENT_DATE - days
$$ LANGUAGE sql;

-- Step 3: Liked at or above a rating
CREATE FUNCTION liked(min_rating NUMERIC) RETURNS TABLE(user_id INT, song_id INT) AS $$
  -- a NULL rating is not a like, so Daffy's unrated play drops out.
  SELECT DISTINCT user_id, song_id FROM Listens
  WHERE rating >= min_rating
$$ LANGUAGE sql;

Each comment records a choice the definition fixes once, so every query that uses it inherits the same answer. The name is the interface: active_since(7) ∩ listener_of('Beatles') means "active Beatles listeners" without re-deriving either term. A CTE names a query inside one statement; a library keeps its definitions for every query.

What a Library Spans

A handful of definitions is small, but it composes into a large space of questions.