45 lines
1.1 KiB
PL/PgSQL
45 lines
1.1 KiB
PL/PgSQL
-- Additional Supabase functions for ExposedGays
|
|
-- Run after schema.sql
|
|
|
|
-- Nearby profiles using PostGIS
|
|
CREATE OR REPLACE FUNCTION public.nearby_profiles(
|
|
p_lat DOUBLE PRECISION,
|
|
p_lng DOUBLE PRECISION,
|
|
p_radius_km DOUBLE PRECISION DEFAULT 10
|
|
)
|
|
RETURNS SETOF public.profiles AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT p.*
|
|
FROM public.profiles p
|
|
WHERE p.location IS NOT NULL
|
|
AND p.status != 'offline'
|
|
AND ST_DWithin(
|
|
p.location,
|
|
ST_SetSRID(ST_MakePoint(p_lng, p_lat), 4326)::geography,
|
|
p_radius_km * 1000
|
|
)
|
|
ORDER BY p.last_active DESC
|
|
LIMIT 200;
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- Nearby cruising spots
|
|
CREATE OR REPLACE FUNCTION public.nearby_spots(
|
|
p_lat DOUBLE PRECISION,
|
|
p_lng DOUBLE PRECISION,
|
|
p_radius_km DOUBLE PRECISION DEFAULT 25
|
|
)
|
|
RETURNS SETOF public.cruising_spots AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT s.*
|
|
FROM public.cruising_spots s
|
|
WHERE ST_DWithin(
|
|
s.location,
|
|
ST_SetSRID(ST_MakePoint(p_lng, p_lat), 4326)::geography,
|
|
p_radius_km * 1000
|
|
)
|
|
ORDER BY s.active_count DESC, s.rating DESC;
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER; |