diff options
| -rw-r--r-- | supabase/schema.sql | 25 |
1 files changed, 18 insertions, 7 deletions
diff --git a/supabase/schema.sql b/supabase/schema.sql index 388154a..3f91444 100644 --- a/supabase/schema.sql +++ b/supabase/schema.sql @@ -2,7 +2,7 @@ -- PostgreSQL database dump -- --- \restrict wNSUmyvUwXt9aJkcfw8gLKUVSQuRUSZfaqWMfy8PywitG4rFxIkfWY9tkxN08cC +-- \restrict UmtWjVupPXEG8xu4MNItn9BufwfwMNLVelpizcRKVG9oupl8Ui3cSVHqhEZtw15 -- Dumped from database version 17.6 -- Dumped by pg_dump version 17.6 @@ -981,9 +981,13 @@ ALTER FUNCTION "public"."request_feed_refresh"("target_subscription_id" "uuid") -- CREATE OR REPLACE FUNCTION "public"."search_entries"("p_query" "text", "p_result_limit" integer DEFAULT 30) RETURNS TABLE("entry_id" "uuid", "feed_id" "uuid", "feed_title" "text", "custom_title" "text", "entry_title" "text", "entry_url" "text", "author" "text", "summary" "text", "image_url" "text", "published_at" timestamp with time zone, "is_read" boolean, "is_saved" boolean) - LANGUAGE "sql" STABLE SECURITY DEFINER - SET "search_path" TO '' + LANGUAGE "plpgsql" STABLE SECURITY DEFINER + SET "search_path" TO 'public', 'extensions' AS $$ +BEGIN + SET LOCAL pg_trgm.similarity_threshold = 0.1; + + RETURN QUERY SELECT e.id AS entry_id, e.feed_id, @@ -1002,17 +1006,24 @@ CREATE OR REPLACE FUNCTION "public"."search_entries"("p_query" "text", "p_result INNER JOIN public.feeds f ON f.id = e.feed_id LEFT JOIN public.user_entry_states ues ON ues.entry_id = e.id AND ues.user_id = auth.uid() WHERE ( - e.title ILIKE '%' || replace(replace(replace(p_query, '\', '\\'), '%', '\%'), '_', '\_') || '%' ESCAPE '\' - OR e.summary ILIKE '%' || replace(replace(replace(p_query, '\', '\\'), '%', '\%'), '_', '\_') || '%' ESCAPE '\' + COALESCE(e.title, '') % p_query + OR COALESCE(e.summary, '') % p_query OR e.author ILIKE '%' || replace(replace(replace(p_query, '\', '\\'), '%', '\%'), '_', '\_') || '%' ESCAPE '\' ) AND s.hidden_from_timeline = false + AND (e.owner_id IS NULL OR e.owner_id = auth.uid()) AND ( (SELECT tier FROM public.user_profiles WHERE id = auth.uid()) IN ('pro', 'developer') OR e.published_at >= now() - interval '14 days' ) - ORDER BY e.published_at DESC + ORDER BY + greatest( + similarity(COALESCE(e.title, ''), p_query), + similarity(COALESCE(e.summary, ''), p_query) + ) DESC, + e.published_at DESC LIMIT p_result_limit; +END; $$; @@ -3736,5 +3747,5 @@ ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TAB -- PostgreSQL database dump complete -- --- \unrestrict wNSUmyvUwXt9aJkcfw8gLKUVSQuRUSZfaqWMfy8PywitG4rFxIkfWY9tkxN08cC +-- \unrestrict UmtWjVupPXEG8xu4MNItn9BufwfwMNLVelpizcRKVG9oupl8Ui3cSVHqhEZtw15 |