Project

General

Profile

Download (1.3 KB)

Tareas #10 » huella_comentario.sql

Demo MiGestion365 Admin, 01/20/2026 06:22 PM

 
-- DROP FUNCTION giscloud.huella_comentario(text);

CREATE OR REPLACE FUNCTION giscloud.huella_comentario(p_regex text)
RETURNS TABLE(proy_name text, fid integer, _created timestamp without time zone, _modified timestamp without time zone, comentario text, geom geometry)
LANGUAGE plpgsql
AS $function$
DECLARE
sql_parts TEXT[] := ARRAY[]::TEXT[];
final_sql TEXT;
r RECORD;
BEGIN
FOR r IN
SELECT p.proy_name AS proy
FROM giscloud.proyectos p
WHERE p.proy_name IS NOT NULL
AND (p.proy_name LIKE 'gpon%' OR p.proy_name LIKE 'lineales%' OR p.proy_name LIKE 'postes%')
AND to_regclass(format('giscloud.%I', p.proy_name)) IS NOT NULL
LOOP
sql_parts := sql_parts || format($q$
SELECT %L::text AS proy_name,
fid, "_created", "_modified",
comentario::text AS comentario,
geom::geometry(Point,4326) AS geom
FROM giscloud.%I
WHERE comentario ~* $1
$q$, r.proy, r.proy);
END LOOP;

IF array_length(sql_parts, 1) IS NULL THEN
RETURN; -- no hay tablas válidas, retorna vacío
END IF;

final_sql := array_to_string(sql_parts, ' UNION ALL ');
RETURN QUERY EXECUTE final_sql USING p_regex;
END;
$function$
;
(3-3/6)