-- 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$
;