spatialSQL/sicob_ubication.sql at master · erikfva/spatialSQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
SET CLIENT_ENCODING TO 'utf8';
CREATE OR REPLACE FUNCTION public.sicob_ubication(reloid text)
 RETURNS TABLE(sicob_id integer, nom_mun text, nom_prov text, nom_dep text)
 LANGUAGE plpgsql
AS $function$
BEGIN

RETURN QUERY

SELECT * FROM sicob_ubication(reloid,'TRUE');


	EXCEPTION
	WHEN others THEN
            RAISE EXCEPTION 'geoSICOB (sicob_ubication); cobertura:%, error:(%,%)', reloid::text, SQLERRM,SQLSTATE;

END;
$function$
;CREATE OR REPLACE FUNCTION public.sicob_ubication(reloid text, condition text)
 RETURNS TABLE(sicob_id integer, nom_mun text, nom_prov text, nom_dep text)
 LANGUAGE plpgsql
AS $function$
BEGIN

RETURN QUERY

SELECT
	t.sicob_id,
	string_agg(t.nom_mun, ',') as nom_mun,
	string_agg(t.nom_prov, ',') as nom_prov,
	string_agg(t.nom_dep, ',') as nom_dep
FROM
	(SELECT * FROM sicob_ungroup_ubication(reloid, condition)) t
group by t.sicob_id
order by t.sicob_id;


	EXCEPTION
	WHEN others THEN
            RAISE EXCEPTION 'geoSICOB (sicob_ubication); cobertura:%, error:(%,%)', reloid::text, SQLERRM,SQLSTATE;

END;
$function$