Return to Answer

• It's a bad idea to use names of basic functions (sum) or data types (timestamp) as column names.

• BETWEEN includes lower and upper bound. Often, you would rather include the lower, but exclude the upper bound.

• There is a tiny chance for a race condition in the above function. To be prepared for heavy concurrent load, consider this related answer:

• Is SELECT or INSERT in a function prone to race conditions?

• PostgreSQL EXCLUDE USING error: Data type integer has no default operator class
• Optimizing queries on a range of timestamps (two columns)
• 2 B-tree indices OR 1 GiST index on tsrange -- which will perform better?
• Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

• It's a bad idea to use names of basic functions (sum) or data types (timestamp) as column names.

• BETWEEN includes lower and upper bound. Often, you would rather include the lower, but exclude the upper bound.

• There is a tiny chance for a race condition in the above function. To be prepared for heavy concurrent load, consider this related answer:

• Is SELECT or INSERT in a function prone to race conditions?

• PostgreSQL EXCLUDE USING error: Data type integer has no default operator class
• Optimizing queries on a range of timestamps (two columns)
• 2 B-tree indices OR 1 GiST index on tsrange -- which will perform better?
• Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

• PostgreSQL EXCLUDE USING error: Data type integer has no default operator class
• Optimizing queries on a range of timestamps (two columns)
• 2 B-tree indices OR 1 GiST index on tsrange -- which will perform better?
• Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

• PostgreSQL EXCLUDE USING error: Data type integer has no default operator class
• Optimizing queries on a range of timestamps (two columns)
• 2 B-tree indices OR 1 GiST index on tsrange -- which will perform better?
• Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

CREATE OR REPLACE FUNCTION trade_volume (_market_id  int
                                       , _start_time timestamp
                                       , _end_time   timestamp)
  RETURNS numeric(16,8) AS
$func$
BEGIN

RETURN QUERY
SELECT sum                          -- dubious identifier
FROM   market_trades_sum_cache
WHERE  market_id  = _market_id
AND    start_time = _start_time
AND    end_time   = _end_time;

IF NOT FOUND THEN
   INSERT INTO market_trades_sum_cache(market_id, start_time, end_time, sum)
   SELECT market_id
        , _start_time
        , _end_time
        , sum(amount)
   FROM   market_trades
   WHERE  market_id = _market_id
   AND    timestamp BETWEEN _start_time AND _end_time
   GROUP  BY 1
   RETURNING sum;
END IF;

END
$func$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION trade_volume (_market_id  int
                                       , _start_time timestamp
                                       , _end_time   timestamp)
  RETURNS SETOF numeric(16,8) AS
$func$
BEGIN

RETURN QUERY
SELECT sum                          -- dubious identifier
FROM   market_trades_sum_cache
WHERE  market_id  = _market_id
AND    start_time = _start_time
AND    end_time   = _end_time;

IF NOT FOUND THEN
   RETURN QUERY
   INSERT INTO market_trades_sum_cache(market_id, start_time, end_time, sum)
   SELECT market_id
        , _start_time
        , _end_time
        , sum(amount)
   FROM   market_trades
   WHERE  market_id = _market_id
   AND    timestamp BETWEEN _start_time AND _end_time
   GROUP  BY 1
   RETURNING sum;
END IF;

END
$func$ LANGUAGE plpgsql VOLATILE;