Something went wrong. Try again.
a tool for shared writing and social publishing
Something went wrong. Try again.
1.1 kB · 28 lines
SQL
1234567891011121314151617181920212223242526272829-- Add sort_date computed column to documents table-- This column stores the older of publishedAt (from JSON data) or indexed_at-- Used for sorting feeds chronologically by when content was actually published
-- Create an immutable function to parse ISO 8601 timestamps from text-- This is needed because direct ::timestamp cast is not immutable (accepts 'now', 'today', etc.)-- The regex validates the format before casting to ensure immutabilityCREATE OR REPLACE FUNCTION parse_iso_timestamp(text) RETURNS timestamptzLANGUAGE sql IMMUTABLE STRICT AS $$ SELECT CASE -- Match ISO 8601 format: YYYY-MM-DDTHH:MM:SS with optional fractional seconds and Z/timezone WHEN $1 ~ '^\d{4}-\d{2}-\d{2}[T ]\d{2}:\d{2}:\d{2}(\.\d+)?(Z|[+-]\d{2}:?\d{2})?$' THEN $1::timestamptz ELSE NULL END$$;
ALTER TABLE documentsADD COLUMN sort_date timestamptz GENERATED ALWAYS AS ( LEAST( COALESCE(parse_iso_timestamp(data->>'publishedAt'), indexed_at), indexed_at )) STORED;
-- Create index on sort_date for efficient orderingCREATE INDEX documents_sort_date_idx ON documents (sort_date DESC, uri DESC);