88dc3714a1
React/Vite/shadcn-ui site for Gigafibre ISP. Address qualification via PostgreSQL (5.2M AQ addresses, pg_trgm fuzzy search). No Supabase dependency for address search. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
45 lines
1.7 KiB
PL/PgSQL
45 lines
1.7 KiB
PL/PgSQL
|
|
CREATE OR REPLACE FUNCTION public.search_addresses(search_term text, result_limit integer DEFAULT 10)
|
|
RETURNS TABLE(identifiant_unique_adresse text, adresse_formatee text, numero_municipal text, numero_unite text, code_postal text, odonyme_recompose_normal text, nom_municipalite text, longitude double precision, latitude double precision, fiber_available boolean, zone_tarifaire integer, max_speed integer, similarity_score real)
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $function$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT
|
|
a.identifiant_unique_adresse,
|
|
a.adresse_formatee,
|
|
a.numero_municipal,
|
|
a.numero_unite,
|
|
a.code_postal,
|
|
a.odonyme_recompose_normal,
|
|
a.nom_municipalite,
|
|
a.longitude,
|
|
a.latitude,
|
|
(f.id IS NOT NULL) AS fiber_available,
|
|
f.zone_tarifaire,
|
|
f.max_speed,
|
|
GREATEST(
|
|
similarity(COALESCE(a.numero_municipal, '') || ' ' || COALESCE(a.odonyme_recompose_normal, ''), search_term),
|
|
similarity(COALESCE(a.adresse_formatee, ''), search_term)
|
|
) AS similarity_score
|
|
FROM public.addresses a
|
|
LEFT JOIN public.fiber_availability f ON f.uuidadresse = a.identifiant_unique_adresse
|
|
WHERE
|
|
(COALESCE(a.numero_municipal, '') || ' ' || COALESCE(a.odonyme_recompose_normal, '')) % search_term
|
|
OR a.adresse_formatee % search_term
|
|
ORDER BY similarity_score DESC
|
|
LIMIT result_limit;
|
|
END;
|
|
$function$;
|
|
|
|
-- Index on the concatenated column for faster trigram matching
|
|
CREATE INDEX IF NOT EXISTS idx_addresses_num_odonyme_trgm
|
|
ON public.addresses
|
|
USING gin ((COALESCE(numero_municipal, '') || ' ' || COALESCE(odonyme_recompose_normal, '')) public.gin_trgm_ops);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_addresses_adresse_formatee_trgm
|
|
ON public.addresses
|
|
USING gin (adresse_formatee public.gin_trgm_ops);
|