Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP FUNCTION IF EXISTS aec_app_simple_profile_select(
- p_id int,
- p_profile_id int,
- p_lat double precision,
- p_lng double precision);
- CREATE OR REPLACE FUNCTION aec_app_simple_profile_select(
- p_id int,
- p_profile_id int,
- p_lat double precision,
- p_lng double precision)
- RETURNS TABLE(
- id int,
- apelido varchar(100),
- idade smallint,
- sexo boolean,
- pais varchar(100),
- estado varchar(100),
- cidade varchar(100),
- fotos smallint,
- is_favorite int,
- distancia double precision,
- places_nome varchar(150),
- display_location smallint) AS
- $$
- BEGIN
- RETURN QUERY EXECUTE 'SELECT
- usuarios.id as id,
- usuarios.apelido as apelido,
- (date_part(''year'', NOW()) - usuarios.nascano - ((date_part(''month'', NOW()), date_part(''day'', NOW())) < (usuarios.nascmes, usuarios.nascdia))::int)::smallint as idade,
- usuarios.sexo as sexo,
- paises_nomes.nome AS pais,
- estados.nome AS estado,
- cidades.nome AS cidade,
- usuarios.fotos,
- usuariosxfavoritos.id as is_favorite,
- CASE
- WHEN ' || p_lat || ' <> 999999999 AND ' || p_lng || ' <> 999999999 THEN
- round(public.earth_distance(public.ll_to_earth(' || p_lat || ', ' || p_lng || '), public.ll_to_earth(usuariosxsessoes.latitude, usuariosxsessoes.longitude))/1000)
- ELSE NULL
- END AS distancia,
- places_nomes.nome AS places_nome,
- display_location AS display_location
- FROM usuarios where id = ' || p_profile_id || '
- LEFT JOIN usuariosxsessoes ON usuariosxsessoes.id_usr = ' || p_profile_id || '
- LEFT JOIN usuariosxfavoritos ON usuariosxfavoritos.id_usr = ' || p_id || ' AND id_fav = ' || p_profile_id || '
- LEFT JOIN places_nomes ON usuariosxsessoes.place_id = places_nomes.id AND places_nomes.lang = 1
- LEFT JOIN paises_nomes ON paises_nomes.id_pais = usuarios.pais AND paises_nomes.lang = 1
- LEFT JOIN estados ON estados.id = usuarios.estado
- LEFT JOIN cidades ON cidades.id = usuarios.cidade';
- END;
- $$
- LANGUAGE 'plpgsql';
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement