materialized_view.sql

CREATE MATERIALIZED VIEW IF NOT EXISTS latest_prices
AS
  SELECT DISTINCT ON (summary_tokens_sold) summary_tokens_sold, price
  FROM sgd6.open_sea_sale t1
  WHERE block_timestamp = (
    SELECT MAX(block_timestamp)
    FROM sgd6.open_sea_sale
    WHERE summary_tokens_sold = t1.summary_tokens_sold
    AND price > 0
  )
WITH NO DATA;

CREATE UNIQUE INDEX latest_prices_id ON latest_prices (summary_tokens_sold);

REFRESH MATERIALIZED VIEW latest_prices;

REFRESH MATERIALIZED VIEW CONCURRENTLY latest_prices;

EXPLAIN SELECT * FROM latest_prices WHERE IN (...); // Good to always use EXPLAIN to test.

CREATE OR REPLACE FUNCTION update_latest_prices() RETURNS TRIGGER 
AS $$ BEGIN REFRESH MATERIALIZED VIEW CONCURRENTLY latest_prices; 
RETURN NULL; END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_latest_prices 
AFTER INSERT OR UPDATE OR DELETE ON sgd6.open_sea_sale 
FOR EACH STATEMENT EXECUTE PROCEDURE update_latest_prices();