Materialized View operations

prompts_mview.sql

--Create the materialized view
CREATE materialized view <schema_name>.mv_prompts AS
SELECT id, ngram, freq
FROM <schema_name>.prompts
WHERE add_conditions_here
ORDER BY freq DESC;

--Create a search index using GIN
CREATE INDEX prompts_ngram_idx 
ON <schema_name>.mv_prompts 
USING GIN (to_tsvector('english', ngram));
                                                   
--Refresh this view when needed                                                   
REFRESH MATERIALIZED VIEW <schema_name>.mv_prompts WITH DATA;