/* contrib/srm_funct/srm_funct--unpackaged--1.0.sql */

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION srm_funct FROM unpackaged" to load this file. \quit

/* contrib/srm_funct/srm_funct--1.0.sql */

SET search_path TO public, topology, tiger, tiger_data;

CREATE OR REPLACE FUNCTION funciones."10_tablas_union_mv"(
	num_tablas text,
	esch text,
	contenido text,
	nameview text,
	nametable1 text,
	nametable2 text,
	nametable3 text,
	nametable4 text,
	nametable5 text,
	nametable6 text,
	nametable7 text,
	nametable8 text,
	nametable9 text,
	nametable10 text)
    RETURNS text
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ declare tab text; BEGIN --el numero de tablas marca el número de uniones
CASE
  WHEN num_tablas = '2' THEN

EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as 
	select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'"';
ELSE tab := '2';
  END CASE;
  CASE
  WHEN num_tablas = '3' THEN
--las geometrias no validas
EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as 
	select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'"';
ELSE tab := '3';
  END CASE;
  
   CASE
  WHEN num_tablas = '4' THEN
--las geometrias no validas
EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as 
	select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'"';
ELSE tab := '4';
  END CASE;
  
     CASE
  WHEN num_tablas = '5' THEN
--las geometrias no validas
EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as 
	select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'"';

ELSE tab := '5';
END CASE;
     CASE
  WHEN num_tablas = '6' THEN
--las geometrias no validas
EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as 
	select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable6||'"';

ELSE tab := '6';
  END CASE;
       CASE
  WHEN num_tablas = '7' THEN
--las geometrias no validas
EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as 
	select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable6||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable7||'"';
ELSE tab := '7';
  END CASE;
  
       CASE
  WHEN num_tablas = '8' THEN
--las geometrias no validas
EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as 
	select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable6||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable7||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable8||'"';
ELSE tab := '8';
  END CASE;

          CASE
  WHEN num_tablas = '9' THEN
--las geometrias no validas
EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as 
	select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable6||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable7||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable8||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable9||'"';
ELSE tab := '9';
  END CASE;
           CASE
  WHEN num_tablas = '10' THEN
--las geometrias no validas
EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as 
	select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable6||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable7||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable8||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable9||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable10||'"';
ELSE tab := '10';
  END CASE;
  Return tab;
  END; $BODY$;
  
  
CREATE OR REPLACE FUNCTION funciones."10_tablas_union_vw"(
	num_tablas text,
	esch text,
	contenido text,
	nameview text,
	nametable1 text,
	nametable2 text,
	nametable3 text,
	nametable4 text,
	nametable5 text,
	nametable6 text,
	nametable7 text,
	nametable8 text,
	nametable9 text,
	nametable10 text)
    RETURNS text
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ declare tab text; BEGIN --el numero de tablas marca el número de uniones
CASE
  WHEN num_tablas = '2' THEN

EXECUTE 'create  view "'|| esch ||'"."'|| nameview ||'" as 
	select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'"';
ELSE tab := '2';
  END CASE;
  CASE
  WHEN num_tablas = '3' THEN
--las geometrias no validas
EXECUTE 'create  view "'|| esch ||'"."'|| nameview ||'" as 
	select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'"';
ELSE tab := '3';
  END CASE;
  
   CASE
  WHEN num_tablas = '4' THEN
--las geometrias no validas
EXECUTE 'create  view "'|| esch ||'"."'|| nameview ||'" as 
	select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'"';
ELSE tab := '4';
  END CASE;
  
     CASE
  WHEN num_tablas = '5' THEN
--las geometrias no validas
EXECUTE 'create  view "'|| esch ||'"."'|| nameview ||'" as 
	select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'"';

ELSE tab := '5';
END CASE;
     CASE
  WHEN num_tablas = '6' THEN
--las geometrias no validas
EXECUTE 'create  view "'|| esch ||'"."'|| nameview ||'" as 
	select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable6||'"';

ELSE tab := '6';
  END CASE;
       CASE
  WHEN num_tablas = '7' THEN
--las geometrias no validas
EXECUTE 'create  view "'|| esch ||'"."'|| nameview ||'" as 
	select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable6||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable7||'"';
ELSE tab := '7';
  END CASE;
  
       CASE
  WHEN num_tablas = '8' THEN
--las geometrias no validas
EXECUTE 'create  view "'|| esch ||'"."'|| nameview ||'" as 
	select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable6||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable7||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable8||'"';
ELSE tab := '8';
  END CASE;

          CASE
  WHEN num_tablas = '9' THEN
--las geometrias no validas
EXECUTE 'create  view "'|| esch ||'"."'|| nameview ||'" as 
	select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable6||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable7||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable8||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable9||'"';
ELSE tab := '9';
  END CASE;
           CASE
  WHEN num_tablas = '10' THEN
--las geometrias no validas
EXECUTE 'create  view "'|| esch ||'"."'|| nameview ||'" as 
	select '|| contenido ||' FROM "'|| esch ||'"."'|| nametable1||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable2||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable3||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable4||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable5||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable6||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable7||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable8||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable9||'"
UNION ALL
select  '|| contenido ||' FROM "'|| esch ||'"."'|| nametable10||'"';
ELSE tab := '10';
  END CASE;
  Return tab;
  END; $BODY$;
  
  


CREATE OR REPLACE FUNCTION funciones.__alter_column_name_mv(
	esch text,
	name_tables text,
	column_name text,
	new_column_name text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: sirve para renombrar una columna de una tabla, column_name (viejo nombre de la columna), new_column_name (nuevo nombre de la columna) */
	EXECUTE 'ALTER MATERIALIZED VIEW "'|| esch ||'"."'|| name_tables ||'" RENAME COLUMN "'|| column_name ||'" TO "'|| new_column_name||'"';
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__alter_table_name(
	esch text,
	name_tables character varying,
	new_name character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: sirve para renombrar una tabla, name_tables (viejo nombre de la tabla), new_name (nuevo nombre de la tabla) */
		EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'".'|| name_tables ||' RENAME TO "'|| new_name||'"';
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__change_epsg(
	esch text,
	name_table character varying,
	epsg numeric,
	geometria text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$

BEGIN
		/*Descripcion: sirve para cambiar el epsg */
		EXECUTE 'ALTER TABLE "'|| esch ||'"."'||name_table||'" ALTER COLUMN geom TYPE Geometry('||geometria||',' || epsg||')';
		
END;
$BODY$;

CREATE OR REPLACE FUNCTION funciones.__contour_lines(
	geomin geometry[],
	colin numeric[],
	breaks numeric[])
    RETURNS TABLE(geom geometry, break numeric) 
    LANGUAGE 'plpgsql'

    COST 100
    IMMUTABLE 
    ROWS 1000
AS $BODY$
DECLARE
	bucketin integer[];
	gs geometry[];
	g geometry;
	vertex geometry[];
	vv numeric[];
	bu integer[];
	inter numeric[];
	interp12 geometry[];
	interp23 geometry[];
	interp31 geometry[];
	segment geometry[];
	running_merge geometry[];
	i integer;
BEGIN
	WITH
	a AS(
		SELECT
			width_bucket(t.x, breaks) AS bin
		FROM unnest(colin) AS t(x)
	)
	SELECT array_agg(bin) INTO bucketin FROM a;

	WITH
	a AS (SELECT unnest(geomin) AS e),
	b AS (SELECT ST_DelaunayTriangles(ST_Collect(a.e)) AS t FROM a),
	c AS (SELECT (ST_Dump(t)).geom AS v FROM b)
	SELECT array_agg(v) INTO gs FROM c;

	i:= 0;

	FOREACH g IN ARRAY gs
	LOOP

		SELECT
			array_agg(a.v),
			array_agg(b.c), 
			array_agg(b.bk)
		INTO vertex, vv, bu
		FROM
		(
			SELECT (ST_DumpPoints(g)).geom AS v limit 3
		) as a
		CROSS JOIN 
		LATERAL(
			SELECT
				t.*
			FROM
				unnest(geomin, colin, bucketin) AS t(geo, c, bk)
			WHERE ST_Equals(geo, a.v)
			LIMIT 1
		) AS b;

		CONTINUE WHEN bu[1] = bu[2] and bu[1] = bu[3];

		interp12 := _get_cell_intersects(vertex, vv, bu, breaks,1,2);
		interp23 := _get_cell_intersects(vertex, vv, bu, breaks,2,3);
		interp31 := _get_cell_intersects(vertex, vv, bu, breaks,3,1);

		WITH
		a AS(
            SELECT
                t.*
            FROM
            unnest(breaks, interp12, interp23, interp31) AS t(br, p12 , p23, p31)
		),
		b AS(
		SELECT
            CASE
            WHEN
            (p12 IS NOT NULL AND p23 IS NOT NULL AND ST_equals(p12, p23)=false) OR
            (p23 IS NOT NULL AND p31 IS NOT NULL AND ST_equals(p23, p31)=false) OR
            (p31 IS NOT NULL AND p12 IS NOT NULL AND ST_equals(p31, p12)=false)
            THEN ST_MakeLine(ARRAY[p12, p23, p31]::geometry[])
            ELSE null::geometry END AS segm,
            br
		FROM a
		)
		SELECT
		    array_agg(b.segm) into segment
		FROM unnest(breaks) AS c(x) 
        LEFT JOIN b ON b.br = c.x;

		IF i = 0 THEN
            running_merge = segment;
            i := 1;
		ELSE
            WITH
            a AS(
                SELECT
                    ST_CollectionExtract(x, 2) AS x,
                    y
                FROM unnest(running_merge,segment) AS t(x,y)
            ),
            b AS(
                SELECT
                ST_collect(x,y) AS element
                FROM a
            )
            SELECT
                array_agg(element) INTO running_merge
            FROM b;
		END IF;

	END LOOP;

	RETURN QUERY
	WITH a AS(
            SELECT
                br,
                ST_CollectionExtract(geo, 2) AS geo
            FROM unnest(running_merge, breaks) AS t(geo, br)
        ),
        b AS(
            SELECT
                ST_LineMerge(geo) AS geo,
                br
            FROM a
        )
    SELECT
        geo AS geom,
        br AS break
	FROM b;

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__crear_tesela(
	geom geometry,
	x_side double precision,
	y_side double precision,
	OUT geometry)
    RETURNS SETOF geometry 
    LANGUAGE 'plpgsql'

    COST 100
    IMMUTABLE STRICT 
    ROWS 1000
AS $BODY$ DECLARE
x_max DECIMAL;
y_max DECIMAL;
x_min DECIMAL;
y_min DECIMAL;
srid INTEGER := 4326;
input_srid INTEGER;
x_series DECIMAL;
y_series DECIMAL;
geom_cell geometry := ST_GeomFromText(FORMAT('POLYGON((0 0, 0 %s, %s %s, %s 0,0 0))',
											$3, $2, $3, $2), srid);
BEGIN
	CASE ST_SRID (geom) WHEN 0 THEN
		geom := ST_SetSRID (geom, srid);
		RAISE NOTICE'SRID Not Found.';
	ELSE
		RAISE NOTICE'SRID Found.';
	END CASE;
	input_srid := ST_srid ( geom );
	geom := ST_Transform ( geom, srid );
	x_max := ST_XMax ( geom );
	y_max := ST_YMax ( geom );
	x_min := ST_XMin ( geom );
	y_min := ST_YMin ( geom );
	x_series := CEIL ( @( x_max - x_min ) / x_side );
	y_series := CEIL ( @( y_max - y_min ) / y_side );

	RETURN QUERY With foo AS (
		SELECT
		ST_Translate( geom_cell, j * $2 + x_min, i * $3 + y_min ) AS cell
		FROM
			generate_series ( 0, x_series ) AS j,
			generate_series ( 0, y_series ) AS i
		) SELECT ST_CollectionExtract(ST_Collect(ST_Transform ( ST_Intersection(cell, geom), input_srid)), 3)
		FROM foo where ST_intersects (cell, geom);
END;
$BODY$;

CREATE OR REPLACE FUNCTION funciones.__crear_teselas_en_poligono_mv(
	esch text,
	namein text,
	nameout text,
	x text,
	y text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
declare geoma text;
BEGIN
SELECT f_geometry_column into geoma 
FROM geometry_columns 
WHERE f_table_schema = esch 
AND f_table_name = namein; 

EXECUTE 'create materialized view  "'|| esch ||'"."'|| nameout ||'"
as select row_number ()over() as id_tess,* from (select (st_dump (geom)).geom from (select funciones.__crear_tesela(
	'|| geoma ||',
	'|| x ||',
	'|| y ||') as geom from  "'|| esch ||'"."'|| namein ||'")t order by geom desc)t;';

END;
$BODY$;

CREATE OR REPLACE FUNCTION funciones.__crear_teselas_en_poligono_vw(
	esch text,
	namein text,
	nameout text,
	x text,
	y text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
declare geoma text;
BEGIN
SELECT f_geometry_column into geoma 
FROM geometry_columns 
WHERE f_table_schema = esch 
AND f_table_name = namein; 

EXECUTE 'create view  "'|| esch ||'"."'|| nameout ||'"
as select row_number ()over() as id_tess,* from (select (st_dump (geom)).geom from (select funciones.__crear_tesela(
	'|| geoma ||',
	'|| x ||',
	'|| y ||') as geom from  "'|| esch ||'"."'|| namein ||'")t order by geom desc)t;';

END;
$BODY$;


CREATE OR REPLACE FUNCTION funciones.__create_innerjoin_id_mv(
	esch text,
	namemv text,
	nametablegeom text,
	nametablegeom_id text,
	tematic text,
	tematic_id text,
	contenido text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: crea una vista materializada desde un innerjoin, nametablegeom_id (nombre de la columna de la tabla geométrica), tematic_id (nombre de la columna de la tabla temática) */
	EXECUTE 'CREATE MATERIALIZED VIEW  "'|| esch ||'"."'|| namemv ||'" AS SELECT '|| contenido||' 
	From "'|| esch ||'"."'|| nametablegeom ||'" INNER JOIN "'|| esch ||'"."'|| tematic ||'" 
	ON concat( "'|| nametablegeom ||'"."'|| nametablegeom_id ||'" )= concat( "'|| tematic ||'"."'|| tematic_id ||'" )';
	
	EXECUTE 'create unique index on "'|| esch ||'"."'|| namemv ||'" (id_srm)';
exception when others then 
EXECUTE 'CREATE MATERIALIZED VIEW  "'|| esch ||'"."'|| namemv ||'" AS select row_number ()over() as id_srm,* from (SELECT '|| contenido||' 
	From "'|| esch ||'"."'|| nametablegeom ||'" INNER JOIN "'|| esch ||'"."'|| tematic ||'" 
	ON concat( "'|| nametablegeom ||'"."'|| nametablegeom_id ||'" )= concat( "'|| tematic ||'"."'|| tematic_id ||'" ))t';
	
	EXECUTE 'create unique index on "'|| esch ||'"."'|| namemv ||'" (id_srm)';
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_innerjoin_id_vw(
	esch text,
	namemv text,
	nametablegeom text,
	nametablegeom_id text,
	tematic text,
	tematic_id text,
	contenido text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: crea una vista desde un innerjoin, nametablegeom_id (nombre de la columna de la tabla geométrica), tematic_id (nombre de la columna de la tabla temática) */
	EXECUTE 'CREATE VIEW  "'|| esch ||'"."'|| namemv ||'" AS SELECT '|| contenido||' From "'|| esch ||'"."'|| nametablegeom ||'" INNER JOIN "'|| esch ||'"."'|| tematic ||'" ON concat( "'|| nametablegeom ||'"."'|| nametablegeom_id ||'" )= concat( "'|| tematic ||'"."'|| tematic_id ||'" )';
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_innerjoin_mv(
	esch text,
	namemv text,
	nametablegeom text,
	nametablegeom_id text,
	tematic text,
	tematic_id text,
	contenido text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: crea una vista materializada desde un innerjoin, nametablegeom_id (nombre de la columna de la tabla geométrica), tematic_id (nombre de la columna de la tabla temática) */
		EXECUTE 'CREATE MATERIALIZED VIEW  "'|| esch ||'"."'|| namemv ||'" AS select a.*
FRom "'|| esch ||'"."'|| nametablegeom ||'"  as B
     INNER JOIN (SELECT '|| contenido||',similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text)as similarity_index 
	From "'|| esch ||'"."'|| nametablegeom ||'"  INNER JOIN "'|| esch ||'"."'|| tematic ||'" 
	ON  "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A
     ON A."'|| nametablegeom_id ||'" = B."'|| nametablegeom_id ||'"
    INNER JOIN ( 
          select A."'|| nametablegeom_id ||'",MAX(A.similarity)as expertrank
          from (SELECT '|| contenido||',similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text) 
	From "'|| esch ||'"."'|| nametablegeom ||'"  INNER JOIN "'|| esch ||'"."'|| tematic ||'" 
	ON  "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A 
          group by A."'|| nametablegeom_id ||'"
     ) as NewA 
    ON a."'|| nametablegeom_id ||'" = NewA."'|| nametablegeom_id ||'" 
       AND a.similarity_index = NewA.expertrank;';
	   EXECUTE 'create unique index on "'|| esch ||'"."'|| namemv ||'" (id_srm)';
exception when others then 
EXECUTE 'CREATE MATERIALIZED VIEW  "'|| esch ||'"."'|| namemv ||'" AS select row_number ()over() as id_srm,* from (select a.*
FRom "'|| esch ||'"."'|| nametablegeom ||'"  as B
     INNER JOIN (SELECT '|| contenido||',similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text)as similarity_index 
	From "'|| esch ||'"."'|| nametablegeom ||'"  INNER JOIN "'|| esch ||'"."'|| tematic ||'" 
	ON  "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A
     ON A."'|| nametablegeom_id ||'" = B."'|| nametablegeom_id ||'"
    INNER JOIN ( 
          select A."'|| nametablegeom_id ||'",MAX(A.similarity)as expertrank
          from (SELECT '|| contenido||',similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text) 
	From "'|| esch ||'"."'|| nametablegeom ||'"  INNER JOIN "'|| esch ||'"."'|| tematic ||'" 
	ON  "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A 
          group by A."'|| nametablegeom_id ||'"
     ) as NewA 
    ON a."'|| nametablegeom_id ||'" = NewA."'|| nametablegeom_id ||'" 
       AND a.similarity_index = NewA.expertrank)t;';
	   EXECUTE 'create unique index on "'|| esch ||'"."'|| namemv ||'" (id_srm)';
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_innerjoin_vw(
	esch text,
	namemv text,
	nametablegeom text,
	nametablegeom_id text,
	tematic text,
	tematic_id text,
	contenido text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: crea una vista  desde un innerjoin, nametablegeom_id (nombre de la columna de la tabla geométrica), tematic_id (nombre de la columna de la tabla temática) */
	EXECUTE 'CREATE VIEW  "'|| esch ||'"."'|| namemv ||'" AS select a.*
FRom "'|| esch ||'"."'|| nametablegeom ||'"  as B
     INNER JOIN (SELECT '|| contenido||',similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text)as similarity_index 
	From "'|| esch ||'"."'|| nametablegeom ||'"  INNER JOIN "'|| esch ||'"."'|| tematic ||'" 
	ON  "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A
     ON A."'|| nametablegeom_id ||'" = B."'|| nametablegeom_id ||'"
    INNER JOIN ( 
          select A."'|| nametablegeom_id ||'",MAX(A.similarity)as expertrank
          from (SELECT '|| contenido||',similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text) 
	From "'|| esch ||'"."'|| nametablegeom ||'"  INNER JOIN "'|| esch ||'"."'|| tematic ||'" 
	ON  "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A 
          group by A."'|| nametablegeom_id ||'"
     ) as NewA 
    ON a."'|| nametablegeom_id ||'" = NewA."'|| nametablegeom_id ||'" 
       AND a.similarity_index = NewA.expertrank;';
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_leftjoin__id_vw(
	esch text,
	namemv text,
	nametablegeom text,
	nametablegeom_id text,
	tematic text,
	tematic_id text,
	contenido text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: crea una vista desde un leftjoin, nametablegeom_id (nombre de la columna de la tabla geométrica), tematic_id (nombre de la columna de la tabla temática) */
	EXECUTE 'CREATE VIEW  "'|| esch ||'"."'|| namemv ||'" AS SELECT '|| contenido||' From "'|| esch ||'"."'|| nametablegeom ||'" LEFT JOIN "'|| esch ||'"."'|| tematic ||'" ON concat( "'|| nametablegeom ||'"."'|| nametablegeom_id ||'" )= concat( "'|| tematic ||'"."'|| tematic_id ||'" )';
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_leftjoin_id_mv(
	esch text,
	namemv text,
	nametablegeom text,
	nametablegeom_id text,
	tematic text,
	tematic_id text,
	contenido text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: crea una vista materializada desde un innerjoin, nametablegeom_id (nombre de la columna de la tabla geométrica), tematic_id (nombre de la columna de la tabla temática) */
	EXECUTE 'CREATE MATERIALIZED VIEW  "'|| esch ||'"."'|| namemv ||'" AS SELECT '|| contenido||' From "'|| esch ||'"."'|| nametablegeom ||'" LEFT JOIN "'|| esch ||'"."'|| tematic ||'" ON concat( "'|| nametablegeom ||'"."'|| nametablegeom_id ||'" )= concat( "'|| tematic ||'"."'|| tematic_id ||'" )';
	
	EXECUTE 'create unique index on "'|| esch ||'"."'|| namemv ||'" (id_srm)';
exception when others then 
EXECUTE 'CREATE MATERIALIZED VIEW  "'|| esch ||'"."'|| namemv ||'" AS select  row_number ()over() as id_srm, * from (SELECT '|| contenido||' From "'|| esch ||'"."'|| nametablegeom ||'" LEFT JOIN "'|| esch ||'"."'|| tematic ||'" ON concat( "'|| nametablegeom ||'"."'|| nametablegeom_id ||'" )= concat( "'|| tematic ||'"."'|| tematic_id ||'" ))t';
		
	EXECUTE 'create unique index on "'|| esch ||'"."'|| namemv ||'" (id_srm)';
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_leftjoin_mv(
	esch text,
	namemv text,
	nametablegeom text,
	nametablegeom_id text,
	tematic text,
	tematic_id text,
	contenido text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: crea una vista materializada desde un innerjoin, nametablegeom_id (nombre de la columna de la tabla geométrica), tematic_id (nombre de la columna de la tabla temática) */
		EXECUTE 'CREATE MATERIALIZED VIEW  "'|| esch ||'"."'|| namemv ||'" AS select a.*
FRom "'|| esch ||'"."'|| nametablegeom ||'"  as B
     INNER JOIN (SELECT '|| contenido||',COALESCE(similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text),0)as similarity_index 
	From "'|| esch ||'"."'|| nametablegeom ||'"  LEFT JOIN "'|| esch ||'"."'|| tematic ||'" 
	ON  "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A
     ON A."'|| nametablegeom_id ||'" = B."'|| nametablegeom_id ||'"
    INNER JOIN ( 
          select A."'|| nametablegeom_id ||'",MAX(A.similarity)as expertrank
          from (SELECT '|| contenido||',COALESCE(similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text),0) as similarity 
	From "'|| esch ||'"."'|| nametablegeom ||'"  LEFT JOIN "'|| esch ||'"."'|| tematic ||'" 
	ON  "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A 
          group by A."'|| nametablegeom_id ||'"
     ) as NewA 
    ON a."'|| nametablegeom_id ||'" = NewA."'|| nametablegeom_id ||'" 
       AND a.similarity_index = NewA.expertrank;';
	   EXECUTE 'create unique index on "'|| esch ||'"."'|| namemv ||'" (id_srm)';
exception when others then 
EXECUTE 'CREATE MATERIALIZED VIEW  "'|| esch ||'"."'|| namemv ||'" AS select row_number ()over() as id_srm,* from (select a.*
FRom "'|| esch ||'"."'|| nametablegeom ||'"  as B
     INNER JOIN (SELECT '|| contenido||',COALESCE(similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text),0)as similarity_index 
	From "'|| esch ||'"."'|| nametablegeom ||'"  LEFT JOIN "'|| esch ||'"."'|| tematic ||'" 
	ON  "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A
     ON A."'|| nametablegeom_id ||'" = B."'|| nametablegeom_id ||'"
    INNER JOIN ( 
          select A."'|| nametablegeom_id ||'",MAX(A.similarity)as expertrank
          from (SELECT '|| contenido||',COALESCE(similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text),0) as similarity 
	From "'|| esch ||'"."'|| nametablegeom ||'"  LEFT JOIN "'|| esch ||'"."'|| tematic ||'" 
	ON  "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A 
          group by A."'|| nametablegeom_id ||'"
     ) as NewA 
    ON a."'|| nametablegeom_id ||'" = NewA."'|| nametablegeom_id ||'" 
       AND a.similarity_index = NewA.expertrank)t;';
	   EXECUTE 'create unique index on "'|| esch ||'"."'|| namemv ||'" (id_srm)';
END;
$BODY$;

	


CREATE OR REPLACE FUNCTION funciones.__create_leftjoin_vw(
	esch text,
	namemv text,
	nametablegeom text,
	nametablegeom_id text,
	tematic text,
	tematic_id text,
	contenido text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: crea una vista desde un leftjoin, nametablegeom_id (nombre de la columna de la tabla geométrica), tematic_id (nombre de la columna de la tabla temática) */
	EXECUTE 'CREATE VIEW  "'|| esch ||'"."'|| namemv ||'" AS select a.*
FRom "'|| esch ||'"."'|| nametablegeom ||'"  as B
     INNER JOIN (SELECT '|| contenido||',COALESCE(similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text),0)as similarity_index 
	From "'|| esch ||'"."'|| nametablegeom ||'"  LEFT JOIN "'|| esch ||'"."'|| tematic ||'" 
	ON  "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A
     ON A."'|| nametablegeom_id ||'" = B."'|| nametablegeom_id ||'"
    INNER JOIN ( 
          select A."'|| nametablegeom_id ||'",MAX(A.similarity)as expertrank
          from (SELECT '|| contenido||',COALESCE(similarity ("'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text,"'|| tematic ||'"."'|| tematic_id ||'"::text),0) as similarity 
	From "'|| esch ||'"."'|| nametablegeom ||'"  LEFT JOIN "'|| esch ||'"."'|| tematic ||'" 
	ON  "'|| nametablegeom ||'"."'|| nametablegeom_id ||'"::text % "'|| tematic ||'"."'|| tematic_id ||'"::text)A 
          group by A."'|| nametablegeom_id ||'"
     ) as NewA 
    ON a."'|| nametablegeom_id ||'" = NewA."'|| nametablegeom_id ||'" 
       AND a.similarity_index = NewA.expertrank;';
	   END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_mv(
	esch text,
	nameview text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: crea una vista materializada */
	EXECUTE 'CREATE materialized VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT * FROM "'|| esch ||'"."'|| nametable||'"';
	EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" (fid)';
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_mv_contain(
	esch text,
	nameview text,
	nametable text,
	contenido text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: crea una vista materializada, contenido: la selección en bruto de los nombres de las columnas que se quieren llevar a la vista */
	EXECUTE 'CREATE materialized VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT "'|| contenido||'" FROM "'|| esch ||'"."'|| nametable||'"';
	EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" (fid)';
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_schema(
	nombre_usuario character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: crea un esquema, nombre_usuario (nombre del esquema) */
	EXECUTE 'CREATE SCHEMA "'|| nombre_usuario ||'" AUTHORIZATION postgres';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_user(
	nombre_usuario character varying,
	pw character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: crea un usuario pw: password */
	EXECUTE 'CREATE SCHEMA "'|| nombre_usuario ||'" AUTHORIZATION postgres';
	EXECUTE 'CREATE user "'|| nombre_usuario ||'" LOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  CONNECTION LIMIT -1
  PASSWORD ''' || pw ||'''';
  EXECUTE 'GRANT ALL ON SCHEMA "'|| nombre_usuario ||'" TO postgres';
  EXECUTE 'GRANT ALL ON SCHEMA "'|| nombre_usuario ||'" TO "'|| nombre_usuario ||'"';
  EXECUTE 'ALTER USER "'|| nombre_usuario ||'" SET search_path TO "' || nombre_usuario ||'"';
  EXECUTE 'ALTER ROLE "'|| nombre_usuario ||'" IN DATABASE '||current_database()||' SET search_path TO "'|| nombre_usuario ||'"';
  EXECUTE 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO "' || nombre_usuario ||'"';
  EXECUTE 'GRANT all privileges ON ALL TABLES IN SCHEMA "'|| nombre_usuario ||'" TO "'|| nombre_usuario ||'"';
  
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_view(
	esch text,
	nameview text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: crea una vista */
	EXECUTE 'CREATE  VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT * FROM "'|| esch ||'"."'|| nametable||'"';
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_view_contain(
	esch text,
	nameview text,
	nametable text,
	contenido text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: crea una vista, contenido: la selección en bruto de los nombres de las columnas que se quieren llevar a la vista */
	EXECUTE 'CREATE VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT "'|| contenido||'" FROM "'|| esch ||'"."'|| nametable||'"';
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_wrapper_csv(
	ser text,
	exc text,
	esch text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
       /*Descripcion: wrapper local a archivos csv sin limite, exc:direccion del archivo*/
		EXECUTE 'CREATE SERVER '|| ser ||' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''CSV'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER "'|| ser ||'" INTO "'|| esch||'"' ;
		EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"';
		
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_wrapper_csv(
	ser text,
	exc text,
	name_tables character varying,
	esch text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper local a archivos csv con limite de tablas, exc:direccion del archivo, name_tables: el nombre de los archivos a limitar*/
		EXECUTE 'CREATE SERVER ' || ser || ' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''CSV'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ("'|| name_tables ||'") FROM SERVER ' || ser || ' INTO "' || esch||'"';
		EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_wrapper_excel(
	ser text,
	exc text,
	names_tables character varying,
	tipo text,
	esch text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper local a archivos excel con limite de tablas, exc:direccion del archivo con el .xls o .xlsx, 
		name_tables: el nombre de los archivos a limitar en el caso del excell el nombre de las hojas, tipo: XLS o XLSX*/
		EXECUTE 'CREATE SERVER '|| ser ||' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''' || tipo || ''', config_options ''OGR_XLSX_HEADERS=FORCE'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all LIMIT TO ("'|| names_tables ||'")FROM SERVER '|| ser ||' INTO "'|| esch||'"' ;
		EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_wrapper_excel(
	ser text,
	exc text,
	tipo text,
	esch text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper local a archivos excel sin limite de tablas, exc:direccion del archivo con el .xls o .xlsx, tipo: XLS o XLSX*/
		EXECUTE 'CREATE SERVER ' || ser || ' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''' || tipo || ''', config_options ''OGR_XLSX_HEADERS=FORCE'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER ' || ser || ' INTO "'|| esch||'"';
		EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"';		
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_wrapper_geopackage(
	ser text,
	exc text,
	esch text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
       /*Descripcion: wrapper local a archivos geopackage sin limite, exc:direccion del archivo acabado con nombre archivo y .gpkg*/
		EXECUTE 'CREATE SERVER ' || ser || ' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''GPKG'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER ' || ser || ' INTO "'|| esch ||'"';
		EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_wrapper_host(
	ser text,
	host text,
	puerto integer,
	dbase text,
	usr text,
	pass character varying,
	esch1 text,
	esch2 text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		   /*Descripcion: wrapper a otro postgres, usr: usuario del otro postgres, pass: password del otro postgres, 
		   esch1:el esquema que se quiere importar, esch2: el esquema propio*/ 
			
		EXECUTE 'CREATE SERVER ' || ser || ' FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host ''' || host || ''', port ''' || puerto || ''', dbname ''' || dbase || ''')';
		EXECUTE 'CREATE USER MAPPING FOR "'|| dbase ||'" SERVER '|| ser ||' OPTIONS ("user" ''' || usr || ''', password ''' || pass || ''')';
		EXECUTE 'IMPORT FOREIGN SCHEMA "'|| esch1 ||'" FROM SERVER '|| ser ||' INTO "'|| esch2||'"' ;
		EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"';
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_wrapper_host(
	ser text,
	host text,
	puerto integer,
	dbase text,
	usr text,
	pass character varying,
	esch1 text,
	esch2 text,
	name_tables character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		   /*Descripcion: wrapper a otro postgres con limitacion de tablas , usr: usuario del otro postgres, pass: password del otro postgres, 
		   esch1:el esquema que se quiere importar, esch2: el esquema propio
		   name_tables: el nombre de los archivos a limitar*/ 
		EXECUTE 'CREATE SERVER ' || ser || ' FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host ''' || host || ''', port ''' || puerto || ''', dbname ''' || dbase || ''')';
		EXECUTE 'CREATE USER MAPPING FOR "'|| dbase ||'" SERVER ' || ser || ' OPTIONS ("user" ''' || usr || ''', password ''' || pass || ''')';
		EXECUTE 'IMPORT FOREIGN SCHEMA "'|| esch1 ||'" LIMIT TO ("'|| name_tables ||'") FROM SERVER '|| ser ||' INTO "'|| esch2||'"';
		EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_wrapper_kml(
	ser text,
	exc text,
	esch text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper local a archivos kml sin limite de tablas, exc:direccion del archivo*/
		EXECUTE 'CREATE SERVER ' || ser || ' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''KML'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER '|| ser ||' INTO "'|| esch||'"';
		EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"';
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_wrapper_mdb(
	ser text,
	exc text,
	esch text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper local a archivos MDB sin limite de tablas, exc:direccion del archivo*/
		EXECUTE 'CREATE SERVER ' || ser || ' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''Geomedia'', config_options ''OGR_SKIP=ODBC GEOMEDIA_DRIVER_TEMPLATE="DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ=%s"'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER ' || ser || ' INTO "'|| esch ||'"';
		EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"';
	
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_wrapper_mdb(
	ser text,
	exc text,
	names_tables character varying,
	esch text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper local a archivos MDB con limite de tablas, exc:direccion del archivo, name_tables: el nombre de los archivos a limitar*/
		EXECUTE 'CREATE SERVER ' || ser || ' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''Geomedia'', config_options ''OGR_SKIP=ODBC GEOMEDIA_DRIVER_TEMPLATE="DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ=%s"'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all LIMIT TO ("'|| names_tables ||'")FROM SERVER ' || ser || ' INTO "'|| esch ||'"';
		EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"';
	
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_wrapper_shp(
	ser text,
	exc text,
	esch text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper local a archivos shp sin limite de tablas, exc:direccion del archivo*/
		EXECUTE 'CREATE SERVER ' || ser || ' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''ESRI Shapefile'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER ' || ser || ' INTO "'|| esch ||'"';
		EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__create_wrapper_shp(
	ser text,
	exc text,
	name_tables character varying,
	esch text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper local a archivos shp con limite de tablas, exc:direccion del archivo, name_tables: el nombre de los archivos a limitar*/
		EXECUTE 'CREATE SERVER ' || ser || ' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''ESRI Shapefile'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ("'|| name_tables ||'") FROM SERVER ' || ser || ' INTO "'|| esch ||'"';
		EXECUTE 'ALTER SERVER '|| ser ||' RENAME TO "'|| ser ||'"';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__drop_mv(
	esch text,
	name_tables text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: eliminar vista materializada en cascada*/
	EXECUTE 'DROP materialized VIEW if exists "'|| esch ||'"."'|| name_tables ||'" cascade';
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__drop_server(
	ser text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: eliminar servidor en cascada*/
	EXECUTE 'DROP SERVER if exists "'|| ser ||'" cascade';
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__drop_user(
	nombre_usuario character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: eliminar usuario*/
  EXECUTE 'drop schema "'|| nombre_usuario ||'" cascade';
  EXECUTE 'REVOKE  GRANT OPTION FOR ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM "'|| nombre_usuario ||'" CASCADE';
  EXECUTE 'REVOKE GRANT OPTION FOR  ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM "'|| nombre_usuario ||'" CASCADE';
  EXECUTE 'REVOKE select ON all tables in schema public FROM "'|| nombre_usuario ||'"';
  EXECUTE 'drop role "'|| nombre_usuario ||'"';
  
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__external_wrapper_csv(
	ser text,
	exc text,
	esch text,
	name_tables character varying,
	new_name character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
       /*Descripcion: wrapper extertno a archivos csv con limite de tablas, exc:direccion del archivo, name_tables: el nombre de los archivos a limitar, new_name:nuevo nombre de la tabla*/
		EXECUTE 'CREATE SERVER acsv FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''CSV'',config_options ''GDAL_HTTP_UNSAFESSL=YES'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ('|| name_tables ||')  FROM SERVER acsv INTO "'|| esch ||'"';
		EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'".'|| name_tables ||' RENAME TO "'|| new_name ||'"';
		EXECUTE 'ALTER SERVER acsv RENAME TO "'|| ser ||'"';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__external_wrapper_excel(
	ser text,
	exc text,
	esch text,
	tipo text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper externo a archivos excel sin limite de tablas, exc:direccion del archivo con el .xls o .xlsx, tipo: XLS o XLSX*/
		EXECUTE 'CREATE SERVER axcl FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''' || tipo || ''',open_options ''OGR_XLSX_HEADERS=FORCE'', config_options ''GDAL_HTTP_UNSAFESSL=YES'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER axcl INTO "'|| esch ||'"';
		EXECUTE 'ALTER SERVER axcl RENAME TO "'|| ser ||'"';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__external_wrapper_excel_limit(
	ser text,
	exc text,
	esch text,
	tipo text,
	name_tables character varying,
	new_name character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper externo a archivos excel con limite de tablas, exc:direccion del archivo con el .xls o .xlsx, tipo: XLS o XLSX
		name_tables: el nombre de los archivos a limitar, new_name:nuevo nombre de la tabla*/
		EXECUTE 'CREATE SERVER axcl FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''' || tipo || ''',open_options ''OGR_XLSX_HEADERS=FORCE'', config_options ''GDAL_HTTP_UNSAFESSL=YES'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ('|| name_tables ||')  FROM SERVER axcl INTO "'|| esch ||'"';
		EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'".'|| name_tables ||' RENAME TO "'|| new_name ||'"';
		EXECUTE 'ALTER SERVER axcl RENAME TO "'|| ser ||'"';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__external_wrapper_geojson(
	ser text,
	exc text,
	esch text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper extertno a archivos geojson sin limite de tablas, exc:direccion del archivo*/
		EXECUTE 'CREATE SERVER ageojson FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''GeoJSON'', config_options ''GDAL_HTTP_UNSAFESSL=YES'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER ageojson INTO "'|| esch ||'"';
		EXECUTE 'ALTER SERVER ageojson RENAME TO "'|| ser ||'"';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__external_wrapper_geojson(
	ser text,
	exc text,
	esch text,
	name_tables character varying,
	new_name character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper extertno a archivos geojson con limite de tablas, exc:direccion del archivo, name_tables: el nombre de los archivos a limitar, new_name:nuevo nombre de la tabla*/
		EXECUTE 'CREATE SERVER ageojson FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''' || exc || ''', format ''GeoJSON'', config_options ''GDAL_HTTP_UNSAFESSL=YES'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ('|| name_tables ||')  FROM SERVER ageojson INTO "'|| esch ||'"';
		EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'".'|| name_tables ||' RENAME TO "'|| new_name ||'"';
		EXECUTE 'ALTER SERVER ageojson RENAME TO "'|| ser ||'"';
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__external_wrapper_geopackage(
	ser text,
	exc text,
	esch text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper extertno a archivos geopackage sin limite de tablas, exc:direccion del archivo con nombre archivo y .gpkg*/
		EXECUTE 'CREATE SERVER ageopackage FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''GPKG'',config_options ''GDAL_HTTP_UNSAFESSL=YES'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all  FROM SERVER ageopackage INTO "'|| esch ||'"';
		EXECUTE 'ALTER SERVER ageopackage RENAME TO "'|| ser ||'"';
		
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__external_wrapper_kml(
	ser text,
	exc text,
	esch text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper extertno a archivos kml sin limite de tablas, exc:direccion del archivo*/
		EXECUTE 'CREATE SERVER akml FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''KML'',config_options ''GDAL_HTTP_UNSAFESSL=YES'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all  FROM SERVER akml INTO "'|| esch ||'"';
		EXECUTE 'ALTER SERVER akml RENAME TO "'|| ser ||'"';
		
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__external_wrapper_kml(
	ser text,
	exc text,
	esch text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper extertno a archivos kml sin limite de tablas, exc:direccion del archivo*/
		EXECUTE 'CREATE SERVER akml FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''KML'',config_options ''GDAL_HTTP_UNSAFESSL=YES'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all  FROM SERVER akml INTO "'|| esch ||'"';
		EXECUTE 'ALTER SERVER akml RENAME TO "'|| ser ||'"';
		
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__external_wrapper_kml_limit(
	ser text,
	exc text,
	esch text,
	name_tables character varying,
	new_name character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper extertno a archivos kml con limite de tablas, exc:direccion del archivo, name_tables: el nombre de los archivos a limitar, new_name:nuevo nombre de la tabla*/
		EXECUTE 'CREATE SERVER akml FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''KML'',config_options ''GDAL_HTTP_UNSAFESSL=YES'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ('|| name_tables ||')  FROM SERVER akml INTO "'|| esch ||'"';
		EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'".'|| name_tables ||' RENAME TO "'|| new_name ||'"';
		EXECUTE 'ALTER SERVER akml RENAME TO "'|| ser ||'"';
		
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__external_wrapper_postgresql(
	ser text,
	host text,
	puerto integer,
	dbase_user text,
	usr_server text,
	usr_user text,
	esch_user text,
	esch_server text,
	name_tables character varying,
	new_name character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper a otro postgres con limitacion de tablas , usr_user: usuario del otro postgres, pass_user: password del otro postgres, 
		   esch_user:el esquema que se quiere importar, esch_server: el esquema propio
		   name_tables: el nombre de los archivos a limitar*/ 
		EXECUTE 'CREATE SERVER apg FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host ''' || host || ''', port ''' || puerto || ''', dbname ''' || dbase_user || ''')';
		EXECUTE 'CREATE USER MAPPING FOR "'|| user_server ||'" SERVER apg OPTIONS ("user" ''' || usr_user || ''', password ''' || pass_user || ''')';
		EXECUTE 'IMPORT FOREIGN SCHEMA "'|| esch_user ||'" LIMIT TO ("'|| name_tables ||'") FROM SERVER apg INTO "'|| esch_server ||'"';
		EXECUTE 'ALTER FOREIGN TABLE "'|| esch_server ||'"."'|| name_tables ||'" RENAME TO "'|| new_name ||'"';
		EXECUTE 'ALTER SERVER apg RENAME TO "'|| ser ||'"';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__external_wrapper_postgresql(
	ser text,
	host text,
	puerto integer,
	dbase_user text,
	usr_server text,
	usr_user text,
	pass_user text,
	esch_user text,
	esch_server text,
	name_tables character varying,
	new_name character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper a otro postgres con limitacion de tablas , usr_user: usuario del otro postgres, pass_user: password del otro postgres, 
		   esch_user:el esquema que se quiere importar, esch_server: el esquema propio
		   name_tables: el nombre de los archivos a limitar*/ 
		EXECUTE 'CREATE SERVER apg FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host ''' || host || ''', port ''' || puerto || ''', dbname ''' || dbase_user || ''')';
		EXECUTE 'CREATE USER MAPPING FOR "'|| usr_server ||'" SERVER apg OPTIONS ("user" ''' || usr_user || ''', password ''' || pass_user || ''')';
		EXECUTE 'IMPORT FOREIGN SCHEMA "'|| esch_user || '" LIMIT TO ("'|| name_tables ||'") FROM SERVER apg INTO "'|| esch_server ||'"';
		EXECUTE 'ALTER FOREIGN TABLE "'|| esch_server ||'"."'|| name_tables ||'" RENAME TO "'|| new_name ||'"';
		EXECUTE 'ALTER SERVER apg RENAME TO "'|| ser ||'"';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__external_wrapper_postgresql_no_limit(
	ser text,
	host text,
	puerto integer,
	dbase_user text,
	usr_server text,
	usr_user text,
	pass_user text,
	esch_user text,
	esch_server text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ BEGIN
		/*Descripcion: wrapper a otro postgres con limitacion de tablas , usr_user: usuario del otro postgres, pass_user: password del otro postgres, 
		   esch_user:el esquema que se quiere importar, esch_server: el esquema propio*/ 
		EXECUTE 'CREATE SERVER apg FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host ''' || host || ''', port ''' || puerto || ''', dbname ''' || dbase_user || ''')';
		EXECUTE 'CREATE USER MAPPING FOR "'|| usr_server ||'" SERVER apg OPTIONS ("user" ''' || usr_user || ''', password ''' || pass_user || ''')';
		EXECUTE 'IMPORT FOREIGN SCHEMA "'|| esch_user || '" FROM SERVER apg INTO "'|| esch_server ||'"';
		EXECUTE 'ALTER SERVER apg RENAME TO "'|| ser ||'"';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__external_wrapper_shp(
	ser text,
	exc text,
	esch text,
	name_tables character varying,
	new_name character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper extertno a archivos shp con limite de tablas, exc:direccion del archivo, name_tables: el nombre de los archivos a limitar, new_name:nuevo nombre de la tabla*/
		EXECUTE 'CREATE SERVER a'|| ser ||' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl/'|| exc ||''', format ''ESRI Shapefile'',config_options ''GDAL_HTTP_UNSAFESSL=YES'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ('|| name_tables ||')  FROM SERVER a'|| ser ||' INTO "'|| esch ||'"';
		EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'".'|| name_tables ||' RENAME TO "'|| new_name ||'"';
		EXECUTE 'ALTER SERVER a'|| ser ||' RENAME TO "'|| ser ||'"';

END;
$BODY$;



CREATE OR REPLACE FUNCTION funciones.__external_wrapper_shp_folder(
	ser text,
	exc text,
	esch text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper extertno a archivos shp sin limite de tablas, exc:direccion del archivo*/
		EXECUTE 'CREATE SERVER a'|| ser ||' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl/' || exc || ''', format ''ESRI Shapefile'',config_options ''GDAL_HTTP_UNSAFESSL=YES'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER a'|| ser ||' INTO "'|| esch ||'"';
		EXECUTE 'ALTER SERVER a'|| ser ||' RENAME TO "'|| ser ||'"';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__external_wrapper_zip(
	ser text,
	exc text,
	esch text,
	new_name character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$	declare coordx character varying
	;
 BEGIN
		/*Descripcion: wrapper extertno a archivos zip que tienen un archivo, exc:direccion del archivo, new_name:nuevo nombre de la tabla, coordx:extrae el nombre de la tabla por si no coincide con el zip*/
		EXECUTE 'CREATE SERVER zip FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsizip//vsicurl_streaming/'|| exc ||''', config_options ''GDAL_HTTP_UNSAFESSL=YES'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER zip INTO "'|| esch ||'"';
		select foreign_table_name into coordx FROM information_schema.foreign_tables where foreign_server_name = 'zip';
		EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'"."'|| coordx ||'" RENAME TO "'|| new_name ||'"';
		EXECUTE 'ALTER SERVER zip RENAME TO "'|| ser ||'"';
		

END;  
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__external_wrapper_zip_varios(
	ser text,
	exc text,
	esch text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$	declare coordx character varying
	;
 BEGIN
		/*Descripcion: wrapper extertno a archivos zip que tienen muchas archivos, exc:direccion del archivo*/
		EXECUTE 'CREATE SERVER zip FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsizip//vsicurl/'|| exc ||''', config_options ''GDAL_HTTP_UNSAFESSL=YES'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all FROM SERVER zip INTO "'|| esch ||'"';
		EXECUTE 'ALTER SERVER zip RENAME TO "'|| ser ||'"';
		
		

END;  
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__ftp_wrapper_csv(
	ser text,
	exc text,
	esch text,
	name_tables character varying,
	new_name character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper extertno a archivos shp con limite de tablas, exc:direccion del archivo, name_tables: el nombre de los archivos a limitar, new_name:nuevo nombre de la tabla*/
		EXECUTE 'CREATE SERVER a'|| ser ||' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''CSV'',config_options ''GDAL_HTTP_UNSAFESSL=YES'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ("'|| name_tables ||'")  FROM SERVER a'|| ser ||' INTO "'|| esch ||'"';
		EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'"."'|| name_tables ||'" RENAME TO "'|| new_name ||'"';
		EXECUTE 'ALTER SERVER a'|| ser ||' RENAME TO "'|| ser ||'"';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__ftp_wrapper_excel(
	ser text,
	exc text,
	esch text,
	tipo text,
	name_tables character varying,
	new_name character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper externo a archivos excel con limite de tablas, exc:direccion del archivo con el .xls o .xlsx, tipo: XLS o XLSX
		name_tables: el nombre de los archivos a limitar, new_name:nuevo nombre de la tabla*/
		EXECUTE 'CREATE SERVER a'|| ser ||' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''' || tipo || ''',open_options ''OGR_XLSX_HEADERS=FORCE'', config_options ''GDAL_HTTP_UNSAFESSL=YES'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ('|| name_tables ||')  FROM SERVER a'|| ser ||' INTO "'|| esch ||'"';
		EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'".'|| name_tables ||' RENAME TO "'|| new_name ||'"';
		EXECUTE 'ALTER SERVER a'|| ser ||' RENAME TO "'|| ser ||'"';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__ftp_wrapper_kml_limit(
	ser text,
	exc text,
	esch text,
	name_tables character varying,
	new_name character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper extertno a archivos kml con limite de tablas, exc:direccion del archivo, name_tables: el nombre de los archivos a limitar, new_name:nuevo nombre de la tabla*/
		EXECUTE 'CREATE SERVER a'|| ser ||' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''KML'',config_options ''GDAL_HTTP_UNSAFESSL=YES'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ('|| name_tables ||')  FROM SERVER a'|| ser ||' INTO "'|| esch ||'"';
		EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'".'|| name_tables ||' RENAME TO "'|| new_name ||'"';
		EXECUTE 'ALTER SERVER a'|| ser ||' RENAME TO "'|| ser ||'"';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__ftp_wrapper_shp(
	ser text,
	exc text,
	esch text,
	name_tables character varying,
	new_name character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
		/*Descripcion: wrapper extertno a archivos shp con limite de tablas, exc:direccion del archivo, name_tables: el nombre de los archivos a limitar, new_name:nuevo nombre de la tabla*/
		EXECUTE 'CREATE SERVER a'|| ser ||' FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource ''/vsicurl_streaming/' || exc || ''', format ''ESRI Shapefile'',config_options ''GDAL_HTTP_UNSAFESSL=YES'')';
		EXECUTE 'IMPORT FOREIGN SCHEMA ogr_all limit to ("'|| name_tables ||'")  FROM SERVER a'|| ser ||' INTO "'|| esch ||'"';
		EXECUTE 'ALTER FOREIGN TABLE "'|| esch ||'"."'|| name_tables ||'" RENAME TO "'|| new_name ||'"';
		EXECUTE 'ALTER SERVER a'|| ser ||' RENAME TO "'|| ser ||'"';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.__list_schemas(
	)
    RETURNS TABLE(schema_name_0 character varying) 
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$ BEGIN

	RETURN QUERY 

select schema_name::character varying
from information_schema.schemata where not schema_name  like 'pg%';
END;
$BODY$;



CREATE OR REPLACE FUNCTION funciones.__list_tbl_view_matview(
	_schema text)
    RETURNS TABLE(nombre character varying, tipo character varying) 
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$ BEGIN

	RETURN query 

select * from (SELECT
            table_name::character varying as nombre,table_type as tipo
        FROM
            information_schema.tables
        WHERE
            table_schema = _schema
union all 
select matviewname::character varying as nombre, 'MATVIEW' as tipo 
from pg_matviews
where schemaname = _schema)t;
END;
$BODY$;



CREATE OR REPLACE FUNCTION funciones.alter_name_rail2_vw(
	esch text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ BEGIN
	/*Descripcion:cambia el nombre de una vista pensado para las de la union */
	EXECUTE 'ALTER VIEW "'|| esch ||'"."paraunionrail_'|| nametable ||'"
    RENAME TO "zvw_'|| nametable ||'";';

END;
$BODY$;


CREATE OR REPLACE FUNCTION funciones.alter_name_vw(
	esch text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ BEGIN
	/*Descripcion:cambia el nombre de una vista pensado para las de la union */
	EXECUTE 'ALTER VIEW "'|| esch ||'"."paraunion_'|| nametable ||'"
    RENAME TO "zvw_'|| nametable ||'";';

END;
$BODY$;



CREATE OR REPLACE FUNCTION funciones.cambiar_esquema_multiples_tablas(
	_schema_salida text,
	schema_entrada text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
DECLARE
    row     record;
BEGIN
    FOR row IN 
        SELECT
            table_schema,
            table_name
        FROM
            information_schema.tables
        WHERE
            table_schema = ''||schema_entrada ||''
     
    LOOP
        EXECUTE 'alter TABLE "'||schema_entrada ||'"."'||row.table_name||'" set schema "'||_schema_salida ||'"';
    END LOOP;
END;
$BODY$;


CREATE OR REPLACE FUNCTION funciones.chequear_geometrias_mv(
	validar boolean,
	esch text,
	nameview text,
	nametable text)
    RETURNS text
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ declare tab text; BEGIN --cuando validar es 0 extrae la vista de todas las geometrías, cuando es 1 solo las falsas
CASE
  WHEN validar = '0' THEN
--todas geometrías sean validas o no
 EXECUTE 'CREATE MATERIALIZED VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT row_number() OVER () AS id'|| nametable||', (case when st_isvalid (st_force2d (geom)) is null then ''f'' else st_isvalid (st_force2d (geom)) end) AS valido,* FROM "'|| esch ||'"."'|| nametable||'"';
EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" (id'|| nametable||')';

ELSE tab := 'falsa';
  END CASE;
  CASE
  WHEN validar = '1' THEN
--las geometrias no validas
 EXECUTE 'CREATE MATERIALIZED VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT row_number() OVER () AS id'|| nametable||', (case when st_isvalid (st_force2d (geom)) is null then ''f'' else st_isvalid (st_force2d (geom)) end) AS valido,* FROM "'|| esch ||'"."'|| nametable||'"where st_isvalid (geom)= ''false'' or st_isvalid (geom) is null';  
EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" (id'|| nametable||')';

ELSE tab := 'todas';
  END CASE;
  Return tab;
  END; $BODY$;

  


CREATE OR REPLACE FUNCTION funciones.chequear_geometrias_vw(
	validar boolean,
	esch text,
	nameview text,
	nametable text)
    RETURNS text
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ declare tab text; BEGIN --cuando validar es 0 extrae la vista de todas las geometrías, cuando es 1 solo las falsas
CASE
  WHEN validar = '0' THEN
--todas geometrías sean validas o no
 EXECUTE 'CREATE VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT row_number() OVER () AS id'|| nametable||', (case when st_isvalid (st_force2d (geom)) is null then ''f'' else st_isvalid (st_force2d (geom)) end) AS valido,* FROM "'|| esch ||'"."'|| nametable||'"';

ELSE tab := 'falsa';
  END CASE;
  CASE
  WHEN validar = '1' THEN
--las geometrias no validas
 EXECUTE 'CREATE VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT row_number() OVER () AS id'|| nametable||', (case when st_isvalid (st_force2d (geom)) is null then ''f'' else st_isvalid (st_force2d (geom)) end) AS valido,* FROM "'|| esch ||'"."'|| nametable||'"where st_isvalid (geom)= ''false'' or st_isvalid (geom) is null';  

ELSE tab := 'todas';
  END CASE;
  Return tab;
  END; $BODY$;




CREATE OR REPLACE FUNCTION funciones.chequear_topologia_mv(
	esch text,
	nameview text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ BEGIN
	/*Descripcion: Saca errores topológicos */

Execute 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select row_number() OVER () AS id'|| nametable||', id, error from( 
select gid1 as id,concat(''geometries with union points'','':'','' id '',gid1,'' y '',gid2) as "error", seudogeom as geom from 
(select c1.id as gid1, max (c2.id) as gid2, c1.geom as seudogeom,
count (*) as num from  (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") c1, 
 (select (st_dumppoints(geom)).geom as geoml, geom, id from "'|| esch ||'"."'|| nametable ||'") c2 where c1.id<>c2.id
and c1.geom && c2.geom and (st_distance (c1.geom,c2.geoml))=0 
group by c1.id,c1.geom)as tabla where num = 1
								  

union all

select id1 as id,''Junctions without union'' as errores, geom from (select r1.id as id1, r2.id as id2, r1.geom from (select a.id,(st_dumppoints(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0)))).geom as geom
from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b 
where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.id>b.id) r1
left join (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") r2
on st_distance (r1.geom,r2.geom) = 0) as tabla where id2 is null 

union all

select id1 as id,''Points without union'' as errores, point as geom from(select r1.geom as point, r1.id as id1, r2.id as id2
from (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") r1 
	  left join "'|| esch ||'"."'|| nametable ||'" r2 
on r1.id<>r2.id and st_expand (r1.geom,1000) && r2.geom and st_distance (r1.geom,r2.geom) = 0)
as tabla where id2 is null

union all

select distinct on (ra.geom)  ra.id, 
  ''Hollow space'' as errores,st_centroid (ra.geom) as geom from 
(select  re.geom, re.contiene, ST_touches (ST_Buffer(st_force2d(st_makevalid(a.geom)),0),re.geom), ST_Buffer(st_force2d(st_makevalid(a.geom)),0),id 
 FROM "'|| esch ||'"."'|| nametable ||'" a, (SELECT  st_contains (((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom),
		ST_ExteriorRing((st_dump(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0))))).geom)) as contiene, 
		((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom)  AS geom
	   FROM "'|| esch ||'"."'|| nametable ||'") re) ra
 where ra.contiene is null and ra.st_touches = ''true''

union all

select gida as id,''Isolated geometry'' as "error",ST_PointOnSurface(st_makepolygon((st_dump(ST_CollectionExtract(contorno,2))).geom)) from (select t1.id as gida, st_multi (st_boundary(t1.geom)) as contorno, t2.id as gidb
from "'|| esch ||'"."'|| nametable ||'" t1 left join "'|| esch ||'"."'|| nametable ||'" t2
on t1.id<>t2.id and st_intersects (t1.geom,t2.geom)) as tabla where gidb is null

union all

select id,''Repeated vertex'' as "error", ST_PointOnSurface(geomp) as geom 
from (select *, (st_dumppoints (geom)).geom as geomp from "'|| esch ||'"."'|| nametable ||'" 
where not st_asewkb(st_removerepeatedpoints (geom)) = st_asewkb (geom))r
GROUP BY id,geomp
     HAVING COUNT(*)>1
	 
union all

select a.id,concat(''
duplicate geometry'','':'','' id original '',b.id) as "error", ST_PointOnSurface(a.geom) as geom
from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b 
where a.id>b.id and ST_Equals(ST_SnapToGrid(a.geom, 0.005), ST_SnapToGrid(b.geom, 0.005))

union all

select a.id,concat(''overlapping geometries'','':'','' id '',a.id,'' y '',b.id) as "error",ST_PointOnSurface(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0))) as geom
from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b 
where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.id>b.id)re';
EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" (id'|| nametable||')';
END;
$BODY$;



CREATE OR REPLACE FUNCTION funciones.chequear_topologia_mv(
	esch text,
	nameview text,
	nametable text,
	pk text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ BEGIN
	/*Descripcion: Saca errores topológicos */

Execute 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select row_number() OVER () AS id'|| nametable||', '||pk||', error from( 
select gid1 as '||pk||',concat(''geometries with union points'','':'','' pk '',gid1,'' y '',gid2) as "error", seudogeom as geom from 
(select c1.'||pk||' as gid1, max (c2.'||pk||') as gid2, c1.geom as seudogeom,
count (*) as num from  (select (st_dumppoints(geom)).geom, '||pk||' from "'|| esch ||'"."'|| nametable ||'") c1, 
 (select (st_dumppoints(geom)).geom as geoml, geom, '||pk||' from "'|| esch ||'"."'|| nametable ||'") c2 where c1.'||pk||'<>c2.'||pk||'
and c1.geom && c2.geom and (st_distance (c1.geom,c2.geoml))=0 
group by c1.'||pk||',c1.geom)as tabla where num = 1
								  

union all

select '||pk||'1 as '||pk||',''Junctions without union'' as errores, geom from (select r1.'||pk||' as '||pk||'1, r2.'||pk||' as '||pk||'2, r1.geom from (select a.'||pk||',(st_dumppoints(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0)))).geom as geom
from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b 
where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.'||pk||'>b.'||pk||') r1
left join (select (st_dumppoints(geom)).geom, '||pk||' from "'|| esch ||'"."'|| nametable ||'") r2
on st_distance (r1.geom,r2.geom) = 0) as tabla where '||pk||'2 is null 

union all

select '||pk||'1 as '||pk||',''Points without union'' as errores, point as geom from(select r1.geom as point, r1.'||pk||' as '||pk||'1, r2.'||pk||' as '||pk||'2
from (select (st_dumppoints(geom)).geom, '||pk||' from "'|| esch ||'"."'|| nametable ||'") r1 
	  left join "'|| esch ||'"."'|| nametable ||'" r2 
on r1.'||pk||'<>r2.'||pk||' and st_expand (r1.geom,1000) && r2.geom and st_distance (r1.geom,r2.geom) = 0)
as tabla where '||pk||'2 is null

union all

select distinct on (ra.geom)  ra.'||pk||', 
  ''Hollow space'' as errores,st_centroid (ra.geom) as geom from 
(select  re.geom, re.contiene, ST_touches (ST_Buffer(st_force2d(st_makevalid(a.geom)),0),re.geom), ST_Buffer(st_force2d(st_makevalid(a.geom)),0),'||pk||' 
 FROM "'|| esch ||'"."'|| nametable ||'" a, (SELECT  st_contains (((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom),
		ST_ExteriorRing((st_dump(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0))))).geom)) as contiene, 
		((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom)  AS geom
	   FROM "'|| esch ||'"."'|| nametable ||'") re) ra
 where ra.contiene is null and ra.st_touches = ''true''

union all

select gida as '||pk||',''Isolated geometry'' as "error",ST_PointOnSurface(st_makepolygon((st_dump(ST_CollectionExtract(contorno,2))).geom)) from (select t1.'||pk||' as gida, st_multi (st_boundary(t1.geom)) as contorno, t2.'||pk||' as gidb
from "'|| esch ||'"."'|| nametable ||'" t1 left join "'|| esch ||'"."'|| nametable ||'" t2
on t1.'||pk||'<>t2.'||pk||' and st_intersects (t1.geom,t2.geom)) as tabla where gidb is null

union all

select '||pk||',''Repeated vertex'' as "error", ST_PointOnSurface(geomp) as geom 
from (select *, (st_dumppoints (geom)).geom as geomp from "'|| esch ||'"."'|| nametable ||'" 
where not st_asewkb(st_removerepeatedpoints (geom)) = st_asewkb (geom))r
GROUP BY '||pk||',geomp
     HAVING COUNT(*)>1
	 
union all

select a.'||pk||',concat(''
duplicate geometry'','':'','' '||pk||' original '',b.'||pk||') as "error", ST_PointOnSurface(a.geom) as geom
from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b 
where a.'||pk||'>b.'||pk||' and ST_Equals(ST_SnapToGrid(a.geom, 0.005), ST_SnapToGrid(b.geom, 0.005))

union all

select a.'||pk||',concat(''overlapping geometries'','':'','' pk '',a.'||pk||','' y '',b.'||pk||') as "error",ST_PointOnSurface(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0))) as geom
from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b 
where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.'||pk||'>b.'||pk||')re';
EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" (id'|| nametable||')';
END;
$BODY$;


CREATE OR REPLACE FUNCTION funciones.chequear_topologia_vw(
	esch text,
	nameview text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ BEGIN
	/*Descripcion: Saca errores topológicos */

Execute 'create view "'|| esch ||'"."'|| nameview ||'" as select row_number() OVER () AS id'|| nametable||', id, error from( 
select gid1 as id,concat(''geometries with union points'','':'','' id '',gid1,'' y '',gid2) as "error", seudogeom as geom from 
(select c1.id as gid1, max (c2.id) as gid2, c1.geom as seudogeom,
count (*) as num from  (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") c1, 
 (select (st_dumppoints(geom)).geom as geoml, geom, id from "'|| esch ||'"."'|| nametable ||'") c2 where c1.id<>c2.id
and c1.geom && c2.geom and (st_distance (c1.geom,c2.geoml))=0 
group by c1.id,c1.geom)as tabla where num = 1
								  

union all

select id1 as id,''Junctions without union'' as errores, geom from (select r1.id as id1, r2.id as id2, r1.geom from (select a.id,(st_dumppoints(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0)))).geom as geom
from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b 
where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.id>b.id) r1
left join (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") r2
on st_distance (r1.geom,r2.geom) = 0) as tabla where id2 is null 

union all

select id1 as id,''Points without union'' as errores, point as geom from(select r1.geom as point, r1.id as id1, r2.id as id2
from (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") r1 
	  left join "'|| esch ||'"."'|| nametable ||'" r2 
on r1.id<>r2.id and st_expand (r1.geom,1000) && r2.geom and st_distance (r1.geom,r2.geom) = 0)
as tabla where id2 is null

union all

select distinct on (ra.geom)  ra.id, 
  ''Hollow space'' as errores,st_centroid (ra.geom) as geom from 
(select  re.geom, re.contiene, ST_touches (ST_Buffer(st_force2d(st_makevalid(a.geom)),0),re.geom), ST_Buffer(st_force2d(st_makevalid(a.geom)),0),id 
 FROM "'|| esch ||'"."'|| nametable ||'" a, (SELECT  st_contains (((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom),
		ST_ExteriorRing((st_dump(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0))))).geom)) as contiene, 
		((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom)  AS geom
	   FROM "'|| esch ||'"."'|| nametable ||'") re) ra
 where ra.contiene is null and ra.st_touches = ''true''

union all

select gida as id,''Isolated geometry'' as "error",ST_PointOnSurface(st_makepolygon((st_dump(ST_CollectionExtract(contorno,2))).geom)) from (select t1.id as gida, st_multi (st_boundary(t1.geom)) as contorno, t2.id as gidb
from "'|| esch ||'"."'|| nametable ||'" t1 left join "'|| esch ||'"."'|| nametable ||'" t2
on t1.id<>t2.id and st_intersects (t1.geom,t2.geom)) as tabla where gidb is null

union all

select id,''Repeated vertex'' as "error", ST_PointOnSurface(geomp) as geom 
from (select *, (st_dumppoints (geom)).geom as geomp from "'|| esch ||'"."'|| nametable ||'" 
where not st_asewkb(st_removerepeatedpoints (geom)) = st_asewkb (geom))r
GROUP BY id,geomp
     HAVING COUNT(*)>1
	 
union all

select a.id,concat(''
duplicate geometry'','':'','' id original '',b.id) as "error", ST_PointOnSurface(a.geom) as geom
from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b 
where a.id>b.id and ST_Equals(ST_SnapToGrid(a.geom, 0.005), ST_SnapToGrid(b.geom, 0.005))

union all

select a.id,concat(''overlapping geometries'','':'','' id '',a.id,'' y '',b.id) as "error",ST_PointOnSurface(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0))) as geom
from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b 
where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.id>b.id)re';
END;
$BODY$;



CREATE OR REPLACE FUNCTION funciones.chequear_topologia_vw(
	esch text,
	nameview text,
	nametable text,
	pk text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ BEGIN
	/*Descripcion: Saca errores topológicos */

Execute 'create view "'|| esch ||'"."'|| nameview ||'" as select row_number() OVER () AS id'|| nametable||', '||pk||', error from( 
select gid1 as '||pk||',concat(''geometries with union points'','':'','' pk '',gid1,'' y '',gid2) as "error", seudogeom as geom from 
(select c1.'||pk||' as gid1, max (c2.'||pk||') as gid2, c1.geom as seudogeom,
count (*) as num from  (select (st_dumppoints(geom)).geom, '||pk||' from "'|| esch ||'"."'|| nametable ||'") c1, 
 (select (st_dumppoints(geom)).geom as geoml, geom, '||pk||' from "'|| esch ||'"."'|| nametable ||'") c2 where c1.'||pk||'<>c2.'||pk||'
and c1.geom && c2.geom and (st_distance (c1.geom,c2.geoml))=0 
group by c1.'||pk||',c1.geom)as tabla where num = 1
								  

union all

select '||pk||'1 as '||pk||',''Junctions without union'' as errores, geom from (select r1.'||pk||' as '||pk||'1, r2.'||pk||' as '||pk||'2, r1.geom from (select a.'||pk||',(st_dumppoints(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0)))).geom as geom
from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b 
where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.'||pk||'>b.'||pk||') r1
left join (select (st_dumppoints(geom)).geom, '||pk||' from "'|| esch ||'"."'|| nametable ||'") r2
on st_distance (r1.geom,r2.geom) = 0) as tabla where '||pk||'2 is null 

union all

select '||pk||'1 as '||pk||',''Points without union'' as errores, point as geom from(select r1.geom as point, r1.'||pk||' as '||pk||'1, r2.'||pk||' as '||pk||'2
from (select (st_dumppoints(geom)).geom, '||pk||' from "'|| esch ||'"."'|| nametable ||'") r1 
	  left join "'|| esch ||'"."'|| nametable ||'" r2 
on r1.'||pk||'<>r2.'||pk||' and st_expand (r1.geom,1000) && r2.geom and st_distance (r1.geom,r2.geom) = 0)
as tabla where '||pk||'2 is null

union all

select distinct on (ra.geom)  ra.'||pk||', 
  ''Hollow space'' as errores,st_centroid (ra.geom) as geom from 
(select  re.geom, re.contiene, ST_touches (ST_Buffer(st_force2d(st_makevalid(a.geom)),0),re.geom), ST_Buffer(st_force2d(st_makevalid(a.geom)),0),'||pk||' 
 FROM "'|| esch ||'"."'|| nametable ||'" a, (SELECT  st_contains (((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom),
		ST_ExteriorRing((st_dump(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0))))).geom)) as contiene, 
		((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom)  AS geom
	   FROM "'|| esch ||'"."'|| nametable ||'") re) ra
 where ra.contiene is null and ra.st_touches = ''true''

union all

select gida as '||pk||',''Isolated geometry'' as "error",ST_PointOnSurface(st_makepolygon((st_dump(ST_CollectionExtract(contorno,2))).geom)) from (select t1.'||pk||' as gida, st_multi (st_boundary(t1.geom)) as contorno, t2.'||pk||' as gidb
from "'|| esch ||'"."'|| nametable ||'" t1 left join "'|| esch ||'"."'|| nametable ||'" t2
on t1.'||pk||'<>t2.'||pk||' and st_intersects (t1.geom,t2.geom)) as tabla where gidb is null

union all

select '||pk||',''Repeated vertex'' as "error", ST_PointOnSurface(geomp) as geom 
from (select *, (st_dumppoints (geom)).geom as geomp from "'|| esch ||'"."'|| nametable ||'" 
where not st_asewkb(st_removerepeatedpoints (geom)) = st_asewkb (geom))r
GROUP BY '||pk||',geomp
     HAVING COUNT(*)>1
	 
union all

select a.'||pk||',concat(''
duplicate geometry'','':'','' '||pk||' original '',b.'||pk||') as "error", ST_PointOnSurface(a.geom) as geom
from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b 
where a.'||pk||'>b.'||pk||' and ST_Equals(ST_SnapToGrid(a.geom, 0.005), ST_SnapToGrid(b.geom, 0.005))

union all

select a.'||pk||',concat(''overlapping geometries'','':'','' pk '',a.'||pk||','' y '',b.'||pk||') as "error",ST_PointOnSurface(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0))) as geom
from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b 
where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.'||pk||'>b.'||pk||')re';
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.comprobacion_cambios_mv(
	esch text,
	nameview text,
	nametableia text,
	nametableori text,
	percent_detection double precision,
	percent_nochange double precision)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ BEGIN /*percent_detection limite por el cual la interseccion no sera valida, percent_nochange limite por el cual no habra cambio*/
EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as 
SELECT re."IA/I", re."ORI/I", re.cambio, re.geom FROM (select ra.* from 
(SELECT te.geom as geombien, te.cambio FROM (select 

fil.* from (select te."IA/I", te."ORI/I", ''No hay cambio'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||'''
UNION ALL
select te."IA/I", te."ORI/I", ''Nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||'''
UNION ALL
select te."IA/I", te."ORI/I", ''Cambio nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||'''
UNION ALL
select te."IA/I", te."ORI/I", ''Cambio derruido'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||'''
)fil

UNION ALL

select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe IA''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from 
(select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, 
"'|| esch ||'"."'|| nametableori||'" pol6)re group by re.tocan, re.fid, re.geom)ta 
group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re
UNION ALL
select null as "IA/I", null as "ORI/I", ''No existe IA'' as cambio, geom as geom from (select re.inter,(st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I",
''No existe IA'' as cambio, re.geom,re.geomia
from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '''|| percent_detection ||'''
UNION ALL

select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe original''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from 
(select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6			
WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, 
"'|| esch ||'"."'|| nametableia||'" pol6)re group by re.tocan, re.fid, re.geom)ta 
group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re
UNION ALL
select null as "IA/I", null as "ORI/I", ''No existe original'' as cambio, geomia as geom from (select re.inter, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I",
''No existe original'' as cambio, re.geom,re.geomia
from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '|| percent_detection ||')te 
 where cambio not like ''No existe %'')re,
(SELECT ter."IA/I", ter."ORI/I", ter.cambio, ter.geom FROM (select 

fil.* from (select te."IA/I", te."ORI/I", ''No hay cambio'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||'''
UNION ALL
select te."IA/I", te."ORI/I", ''Nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||'''
UNION ALL
select te."IA/I", te."ORI/I", ''Cambio nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||'''
UNION ALL
select te."IA/I", te."ORI/I", ''Cambio derruido'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||'''
)fil

UNION ALL

select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe IA''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from 
(select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, 
"'|| esch ||'"."'|| nametableori||'" pol6)re group by re.tocan, re.fid, re.geom)ta 
group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re
UNION ALL
select null as "IA/I", null as "ORI/I", ''No existe IA'' as cambio, geom as geom from (select re.inter,(st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I",
''No existe IA'' as cambio, re.geom,re.geomia
from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '''|| percent_detection ||'''
UNION ALL

select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe original''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from 
(select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6			
WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, 
"'|| esch ||'"."'|| nametableia||'" pol6)re group by re.tocan, re.fid, re.geom)ta 
group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re
UNION ALL
select null as "IA/I", null as "ORI/I", ''No existe original'' as cambio, geomia as geom from (select re.inter, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I",
''No existe original'' as cambio, re.geom,re.geomia
from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '|| percent_detection ||')ter
where cambio like ''%IA'')ra
where st_intersects(re.geombien,ra.geom) = ''true'' 
union all 
SELECT ta.* FROM (select 

fil.* from (select te."IA/I", te."ORI/I", ''No hay cambio'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||'''
UNION ALL
select te."IA/I", te."ORI/I", ''Nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||'''
UNION ALL
select te."IA/I", te."ORI/I", ''Cambio nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||'''
UNION ALL
select te."IA/I", te."ORI/I", ''Cambio derruido'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||'''
)fil

UNION ALL

select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe IA''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from 
(select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, 
"'|| esch ||'"."'|| nametableori||'" pol6)re group by re.tocan, re.fid, re.geom)ta 
group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re
UNION ALL
select null as "IA/I", null as "ORI/I", ''No existe IA'' as cambio, geom as geom from (select re.inter,(st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I",
''No existe IA'' as cambio, re.geom,re.geomia
from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '''|| percent_detection ||'''
UNION ALL

select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe original''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from 
(select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6			
WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, 
"'|| esch ||'"."'|| nametableia||'" pol6)re group by re.tocan, re.fid, re.geom)ta 
group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re
UNION ALL
select null as "IA/I", null as "ORI/I", ''No existe original'' as cambio, geomia as geom from (select re.inter, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I",
''No existe original'' as cambio, re.geom,re.geomia
from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '|| percent_detection ||')ta)re
     GROUP BY re."IA/I", re."ORI/I", re.cambio, re.geom
     HAVING COUNT(*)=1';
	   
	END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.comprobacion_cambios_vw(
	esch text,
	nameview text,
	nametableia text,
	nametableori text,
	percent_detection double precision,
	percent_nochange double precision)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ BEGIN /*percent_detection limite por el cual la interseccion no sera valida, percent_nochange limite por el cual no habra cambio*/
EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as 
SELECT re."IA/I", re."ORI/I", re.cambio, re.geom FROM (select ra.* from 
(SELECT te.geom as geombien, te.cambio FROM (select 

fil.* from (select te."IA/I", te."ORI/I", ''No hay cambio'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||'''
UNION ALL
select te."IA/I", te."ORI/I", ''Nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||'''
UNION ALL
select te."IA/I", te."ORI/I", ''Cambio nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||'''
UNION ALL
select te."IA/I", te."ORI/I", ''Cambio derruido'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||'''
)fil

UNION ALL

select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe IA''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from 
(select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, 
"'|| esch ||'"."'|| nametableori||'" pol6)re group by re.tocan, re.fid, re.geom)ta 
group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re
UNION ALL
select null as "IA/I", null as "ORI/I", ''No existe IA'' as cambio, geom as geom from (select re.inter,(st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I",
''No existe IA'' as cambio, re.geom,re.geomia
from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '''|| percent_detection ||'''
UNION ALL

select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe original''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from 
(select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6			
WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, 
"'|| esch ||'"."'|| nametableia||'" pol6)re group by re.tocan, re.fid, re.geom)ta 
group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re
UNION ALL
select null as "IA/I", null as "ORI/I", ''No existe original'' as cambio, geomia as geom from (select re.inter, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I",
''No existe original'' as cambio, re.geom,re.geomia
from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '|| percent_detection ||')te 
 where cambio not like ''No existe %'')re,
(SELECT ter."IA/I", ter."ORI/I", ter.cambio, ter.geom FROM (select 

fil.* from (select te."IA/I", te."ORI/I", ''No hay cambio'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||'''
UNION ALL
select te."IA/I", te."ORI/I", ''Nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||'''
UNION ALL
select te."IA/I", te."ORI/I", ''Cambio nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||'''
UNION ALL
select te."IA/I", te."ORI/I", ''Cambio derruido'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||'''
)fil

UNION ALL

select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe IA''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from 
(select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, 
"'|| esch ||'"."'|| nametableori||'" pol6)re group by re.tocan, re.fid, re.geom)ta 
group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re
UNION ALL
select null as "IA/I", null as "ORI/I", ''No existe IA'' as cambio, geom as geom from (select re.inter,(st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I",
''No existe IA'' as cambio, re.geom,re.geomia
from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '''|| percent_detection ||'''
UNION ALL

select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe original''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from 
(select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6			
WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, 
"'|| esch ||'"."'|| nametableia||'" pol6)re group by re.tocan, re.fid, re.geom)ta 
group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re
UNION ALL
select null as "IA/I", null as "ORI/I", ''No existe original'' as cambio, geomia as geom from (select re.inter, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I",
''No existe original'' as cambio, re.geom,re.geomia
from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '|| percent_detection ||')ter
where cambio like ''%IA'')ra
where st_intersects(re.geombien,ra.geom) = ''true'' 
union all 
SELECT ta.* FROM (select 

fil.* from (select te."IA/I", te."ORI/I", ''No hay cambio'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||'''
UNION ALL
select te."IA/I", te."ORI/I", ''Nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geom as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||'''
UNION ALL
select te."IA/I", te."ORI/I", ''Cambio nuevo'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I"<'''|| percent_nochange ||''' and te."ORI/I">'''|| percent_nochange ||'''
UNION ALL
select te."IA/I", te."ORI/I", ''Cambio derruido'' as cambio, te.geom from(select ta."IA/I", ta."ORI/I", ta.geomia as geom from (select re.inter,re.geom,re.geomia, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I"

from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" > '''|| percent_detection ||''' and ta."ORI/I" > '''|| percent_detection ||''')te where te."IA/I">'''|| percent_nochange ||''' and te."ORI/I"<'''|| percent_nochange ||'''
)fil

UNION ALL

select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe IA''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from 
(select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, 
"'|| esch ||'"."'|| nametableori||'" pol6)re group by re.tocan, re.fid, re.geom)ta 
group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re
UNION ALL
select null as "IA/I", null as "ORI/I", ''No existe IA'' as cambio, geom as geom from (select re.inter,(st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I",
''No existe IA'' as cambio, re.geom,re.geomia
from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '''|| percent_detection ||'''
UNION ALL

select null as "IA/I", null as "ORI/I", cambio, re.geom from (select null as porcentaje_cambio, ''No existe original''as cambio, ta.geom as geom from (select re.tocan, re.fid ,re.geom from 
(select st_intersects(pol3.inter, pol6.geom) as tocan, pol6.fid, pol6.geom from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6			
WHERE ST_INTERSECTS(pol3.geom,pol6.geom)) pol3, 
"'|| esch ||'"."'|| nametableia||'" pol6)re group by re.tocan, re.fid, re.geom)ta 
group by ta.fid, ta.geom HAVING COUNT(ta.fid) =1)re
UNION ALL
select null as "IA/I", null as "ORI/I", ''No existe original'' as cambio, geomia as geom from (select re.inter, (st_area(re.inter) / st_area(re.geomia)) * 100 as "IA/I", (st_area(re.inter) / st_area(re.geom)) * 100 as "ORI/I",
''No existe original'' as cambio, re.geom,re.geomia
from (SELECT ST_INTERSECTION(pol3.geom,pol6.geom) as inter,pol6.fid, pol6.geom, 
 pol3.geom as geomia
from "'|| esch ||'"."'|| nametableia||'" pol3 , 
		 "'|| esch ||'"."'|| nametableori||'" pol6				
WHERE ST_INTERSECTS(pol3.geom,pol6.geom))re)ta where ta."IA/I" < '''|| percent_detection ||''' or ta."ORI/I" < '|| percent_detection ||')ta)re
     GROUP BY re."IA/I", re."ORI/I", re.cambio, re.geom
     HAVING COUNT(*)=1';
	   
	END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.comprobacion_de_datos_mv(
	esch text,
	nameview text,
	nametable text,
	contenido character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ declare namecolumn character varying; BEGIN /*Retorna una vista con una columna en la cual informa si el contenido buscado existe o no.
contenido:que se quiere buscar*/
SELECT COLUMN_NAME into namecolumn FROM information_schema.COLUMNS WHERE TABLE_NAME = ''|| nametable ||'' limit 1;
EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select *, ''true'' as exists 
	from "'|| esch ||'"."'|| nametable||'" where '|| contenido ||'
union
select *, ''false'' as exists from "'|| esch ||'"."'|| nametable||'" where '||namecolumn||'
not in (select '||namecolumn||' from "'|| esch ||'"."'|| nametable||'" where '|| contenido ||')';
	END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.comprobacion_de_datos_vw(
	esch text,
	nameview text,
	nametable text,
	contenido character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ declare namecolumn character varying; BEGIN /*Retorna una vista con una columna en la cual informa si el contenido buscado existe o no.
contenido:que se quiere buscar*/
SELECT COLUMN_NAME into namecolumn FROM information_schema.COLUMNS WHERE TABLE_NAME = ''|| nametable ||'' limit 1;
EXECUTE 'create  view "'|| esch ||'"."'|| nameview ||'" as select *, ''true'' as exists 
	from "'|| esch ||'"."'|| nametable||'" where '|| contenido ||'
union
select *, ''false'' as exists from "'|| esch ||'"."'|| nametable||'" where '||namecolumn||'
not in (select '||namecolumn||' from "'|| esch ||'"."'|| nametable||'" where '|| contenido ||')';
	END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.crear_geometrias_validas_mv(
	esch text,
	nameview text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: crea una vista materializada con una columna de geometrías validas */
	EXECUTE 'CREATE  MATERIALIZED VIEW "'|| esch ||'"."'|| nameview ||'" 
	AS SELECT row_number() OVER () AS id'|| nameview ||', ra.st_makevalid as geom from (select st_makevalid (geom),* FROM "'|| esch ||'"."'|| nametable||'") ra';
	EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" (id'|| nameview ||')';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.crear_geometrias_validas_vw(
	esch text,
	nameview text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: crea una vista materializada con una columna de geometrías validas */
	EXECUTE 'CREATE VIEW "'|| esch ||'"."'|| nameview ||'" 
	AS SELECT row_number() OVER () AS id'|| nameview ||', ra.st_makevalid as geom from (select st_makevalid (geom),* FROM "'|| esch ||'"."'|| nametable||'") ra';
END;
$BODY$;



CREATE OR REPLACE FUNCTION funciones.create_for_union_rail2_vw(
	esch text,
	nameview text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ BEGIN
	/*Descripcion:crea una vista para hacer una union posterior */
	EXECUTE 'create view "'|| esch ||'"."paraunionrail_'|| nameview ||'" as 
	select ra.* from 
	(select * FROM "'|| esch ||'"."'|| nametable||'") ra';

END;
$BODY$;



CREATE OR REPLACE FUNCTION funciones.create_for_union_vw(
	esch text,
	nameview text,
	nametable text,
	value text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ BEGIN
	/*Descripcion:crea una vista para hacer una union posterior */
	EXECUTE 'create view "'|| esch ||'"."paraunion_'|| nameview ||'" as 
	select ra.geom, ra.layer_ori from 
	(select geom, '''|| value||''' as layer_ori FROM "'|| esch ||'"."'|| nametable||'") ra';

END;
$BODY$;



CREATE OR REPLACE FUNCTION funciones.cubierta_sin_solapes(
	esch text,
	cubierta text,
	cubiertasalida text,
	redtransporte text,
	hidrografia text,
	epsg numeric)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: une los atributos iniciales a las capas generalizadas */
	EXECUTE 'drop table if exists "'||esch||'"."'||cubierta||'_valid";
	create table "'||esch||'"."'||cubierta||'_valid" as select *, st_makevalid(geom) as geom2 from "'||esch||'"."'||cubierta||'";
alter table "'||esch||'"."'||cubierta||'_valid" drop column geom; 
alter table "'||esch||'"."'||cubierta||'_valid" rename column geom2 to geom;
create index z_red_'||cubierta||'_valid_geom_dif_gidx on "'||esch||'"."'||cubierta||'_valid" using gist(geom);
drop table if exists "'||esch||'"."'||cubiertasalida||'";
create table "'||esch||'"."'||cubiertasalida||'" (like "'||esch||'"."'||cubierta||'_valid");
alter table "'||esch||'"."'||cubiertasalida||'"
add column geom_dif geometry (Geometry,'||epsg||');
create index z_'||cubierta||'_diferencia_geom_dif_gidx
on "'||esch||'"."'||cubiertasalida||'" 
using gist(geom_dif); 
drop table if exists "'||esch||'"."'||redtransporte||'_union";
create table "'||esch||'"."'||redtransporte||'_union" as select st_makevalid(st_union(st_makevalid(geom))) as geom from "'||esch||'"."'||redtransporte||'";

create index z_red_clean_union_geom_dif_gidx
on "'||esch||'"."'||redtransporte||'_union" 
using gist(geom);
insert into "'||esch||'"."'||cubiertasalida||'"  
select a.*,(st_dump(st_difference(a.geom,b.geom))).geom
from "'||esch||'"."'||cubierta||'_valid" a, 
"'||esch||'"."'||redtransporte||'_union" b  
where st_intersects(a.geom,b.geom);
insert into "'||esch||'"."'||cubiertasalida||'" 
select a.*,null from  "'||esch||'"."'||cubierta||'_valid" a 
left join "'||esch||'"."'||redtransporte||'_union" b on st_intersects(a.geom,b.geom) where b.geom is null;
update "'||esch||'"."'||cubiertasalida||'"  set geom_dif = geom where geom_dif is null;
update "'||esch||'"."'||cubiertasalida||'"  set geom = geom_dif;			

drop table if exists "'||esch||'"."'||cubierta||'_diferencia2";
create table "'||esch||'"."'||cubierta||'_diferencia2"(like "'||esch||'"."'||cubiertasalida||'");
alter table "'||esch||'"."'||cubierta||'_diferencia2"
add column geom_dif2 geometry (Geometry,'||epsg||');
create index z_'||cubierta||'_diferencia2_geom_dif_gidx
on "'||esch||'"."'||cubierta||'_diferencia2" 
using gist(geom_dif2);
create table "'||esch||'"."'||hidrografia||'_union" as select st_makevalid(st_union(st_makevalid(geom))) as geom from "'||esch||'"."'||hidrografia||'";
create index z_hidro_general_generaliced_union_geom_dif_gidx
on "'||esch||'"."'||hidrografia||'_union" 
using gist(geom);
insert into "'||esch||'"."'||cubierta||'_diferencia2"  
select a.*,(st_dump(st_difference(a.geom,b.geom))).geom
from "'||esch||'"."'||cubiertasalida||'" a, 
"'||esch||'"."'||hidrografia||'_union" b  
where st_intersects(a.geom,b.geom);
insert into "'||esch||'"."'||cubierta||'_diferencia2" 
select a.*,null from  "'||esch||'"."'||cubiertasalida||'" a 
left join "'||esch||'"."'||hidrografia||'_union" b on st_intersects(a.geom,b.geom) where b.geom is null;
update "'||esch||'"."'||cubierta||'_diferencia2"  set geom_dif2 = geom where geom_dif2 is null;
update "'||esch||'"."'||cubierta||'_diferencia2"  set geom = geom_dif2;	
alter table "'||esch||'"."'||cubierta||'_diferencia2" drop column geom_dif2;
alter table "'||esch||'"."'||cubierta||'_diferencia2" drop column geom_dif;
drop table "'||esch||'"."'||cubiertasalida||'";
drop table "'||esch||'"."'||redtransporte||'_union";
drop table "'||esch||'"."'||hidrografia||'_union";
alter table "'||esch||'"."'||cubierta||'_diferencia2" rename to "'||cubiertasalida||'";';

END;
$BODY$;



CREATE OR REPLACE FUNCTION funciones.dividir_por_tipo_mv(
	esch text,
	nameview text,
	nametable text,
	campo text,
	valor text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: divide la tabla por el topo que se quiera tipo */
	EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'"
as select * from "'|| esch ||'"."'|| nametable||'" where "'|| campo ||'" = '''|| valor ||'''';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.dividir_por_tipo_vw(
	esch text,
	nameview text,
	nametable text,
	campo text,
	valor text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: divide la tabla por el topo que se quiera tipo */
	EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'"
as select * from "'|| esch ||'"."'|| nametable||'" where "'|| campo ||'" = '''|| valor ||'''';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.extraer_centroides_mv(
	esch text,
	nameview text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: extrae puntos que siempre estan dentro de la geometria */
	EXECUTE 'CREATE  MATERIALIZED VIEW "'|| esch ||'"."'|| nameview ||'" AS 
	select *, row_number() OVER () as fidid from(SELECT *, ST_PointOnSurface (geom) as centroid FROM "'|| esch ||'"."'|| nametable||'")te';
	EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" (fidid)';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.extraer_centroides_vw(
	esch text,
	nameview text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
/*Descripcion: extrae puntos que siempre estan dentro de la geometria */
	EXECUTE 'CREATE  VIEW "'|| esch ||'"."'|| nameview ||'" AS 
	SELECT  *, ST_PointOnSurface (geom) as centroid FROM "'|| esch ||'"."'|| nametable||'"';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.extraer_lineas_exteriores_mv(
	esch text,
	nameview text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ declare colum character varying;
BEGIN
 EXECUTE 'create table "'|| esch ||'"."'|| nameview||'_tabla" as select *,ST_boundary(st_force2d(geom)) as geom2
	from "'|| esch ||'"."'||nametable||'"';
	EXECUTE 'alter table "'|| esch ||'"."'|| nameview||'_tabla" drop column geom;';
	EXECUTE 'alter table "'|| esch ||'"."'|| nameview||'_tabla" rename geom2 to geom;';
	EXECUTE 'create  materialized view "'|| esch ||'"."'|| nameview||'" as select * 
	from "'|| esch ||'"."'|| nameview||'_tabla";';
	 
	END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.extraer_lineas_exteriores_vw(
	esch text,
	nameview text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ declare colum character varying;
BEGIN
 EXECUTE 'create table "'|| esch ||'"."'|| nameview||'_tabla" as select *,ST_boundary(st_force2d(geom)) as geom2
	from "'|| esch ||'"."'||nametable||'"';
	EXECUTE 'alter table "'|| esch ||'"."'|| nameview||'_tabla" drop column geom;';
	EXECUTE 'alter table "'|| esch ||'"."'|| nameview||'_tabla" rename geom2 to geom;';
	EXECUTE 'create view "'|| esch ||'"."'|| nameview||'" as select * 
	from "'|| esch ||'"."'|| nameview||'_tabla";';
	 
	END;
$BODY$;



CREATE OR REPLACE FUNCTION funciones.generalizar_areas(
	esch text,
	nametable text,
	tbl_salida text,
	tolerance double precision,
	simply double precision,
	pk_field character varying,
	min_area double precision,
	area_peri double precision,
	epsg integer)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: une los atributos iniciales a las capas generalizadas */
	EXECUTE 'create table "'|| esch ||'"."'|| nametable||'_tipo"

as select "'|| pk_field ||'", st_union (geom) as geom from "'|| esch ||'"."'|| nametable||'" group by "'|| pk_field ||'";

create table "'|| esch ||'"."'|| nametable||'_dissolve" as select "'|| pk_field ||'", (st_dump (geom)).geom as geom from "'|| esch ||'"."'|| nametable||'_tipo";
	
CREATE table "'|| esch ||'"."'|| nametable||'_dissolve_simplify"
	AS  
	SELECT "'|| pk_field ||'",
	st_simplifypreservetopology(geom, '|| simply ||')as geom
	
	FROM "'|| esch ||'"."'|| nametable||'_dissolve";
	CREATE TABLE "'|| esch ||'"."'|| nametable||'_dissolve_simplify2"
	as
	select fid, "'|| pk_field ||'",geom,st_area (geom) as area from (SELECT row_number() OVER () AS fid,
	 "'|| pk_field ||'",
	    ST_GeomFromText(ST_AsText( (ST_Dump(geom)).geom )) as geom
	    
	from "'|| esch ||'"."'|| nametable||'_dissolve_simplify")t;
	
CREATE TABLE "'|| esch ||'"."'|| nametable||'_dissolve_simplify_anillos"

AS
 SELECT "'|| nametable||'_dissolve_simplify2".fid,"'|| pk_field ||'",
    (st_dumprings(st_force2d("'|| nametable||'_dissolve_simplify2".geom))).geom AS geom,
    st_area((st_dumprings(st_force2d("'|| nametable||'_dissolve_simplify2".geom))).geom) AS area
   FROM "'|| esch ||'"."'|| nametable||'_dissolve_simplify2";
	

create table   "'|| esch ||'"."'|| nametable||'_union_simply_anillos" as SELECT fid ,
"'|| pk_field ||'",
   st_force2d(geom) as geom,
   area
FROM
  "'|| esch ||'"."'|| nametable||'_dissolve_simplify2"
UNION
SELECT fid ,
"'|| pk_field ||'",
   ST_Buffer(geom,'|| tolerance||'),
   area
FROM
  "'|| esch ||'"."'|| nametable||'_dissolve_simplify_anillos";
  
  UPDATE "'|| esch ||'"."'|| nametable||'_union_simply_anillos" SET geom=ST_makevalid(geom) WHERE NOT ST_isValid(geom);
    create table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos" as select st_union(geom) as geom, "'|| pk_field ||'" from  "'|| esch ||'"."'|| nametable||'_union_simply_anillos" group by "'|| pk_field ||'" 
	 ; create table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump2" as select (st_dump(geom)).geom as geom, "'|| pk_field ||'" from  "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos"  
;
create table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump" as SELECT row_number() OVER () AS fid,geom, "'|| pk_field ||'" from "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump2"
;create table "'|| esch ||'"."'|| nametable||'_union" as select fid, ST_CollectionExtract(st_union(geom),3) as geom, 
"'|| pk_field ||'",
st_area(ST_CollectionExtract(st_union(geom),3)),
((st_area(ST_CollectionExtract(st_union(geom),3)))/(((ST_Perimeter(ST_CollectionExtract(st_union(geom),3))))^2)) as relation
from "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump"  group by fid,"'|| pk_field ||'";

create table "'|| esch ||'"."'|| nametable||'_union_area" as SELECT * FROM "'|| esch ||'"."'|| nametable||'_union";

CREATE INDEX z_'|| nametable||'_union_area_geom_gidx
    ON "'|| esch ||'"."'|| nametable||'_union_area" USING gist
    (geom);
	
	
	create table "'|| esch ||'"."'||tbl_salida||'" as select a.*, b.geom as geom2 
	from "'|| esch ||'"."'|| nametable||'" a join
	"'|| esch ||'"."'|| nametable||'_union_area" b on a."'|| pk_field ||'" = b."'|| pk_field ||'" and st_intersects (st_setsrid(b.geom,'|| epsg||'),a.geom);
		alter table "'|| esch ||'"."'||tbl_salida||'"  drop geom;
	alter table "'|| esch ||'"."'||tbl_salida||'"  rename geom2 to geom;
	alter table "'|| esch ||'"."'||tbl_salida||'"  add column ida serial;

DELETE FROM  "'|| esch ||'"."'||tbl_salida||'"
WHERE ida IN
    (SELECT ida
    FROM 
        (SELECT ida,
         ROW_NUMBER() OVER( PARTITION BY geom
        ORDER BY  ida ) AS row_num
        FROM "'|| esch ||'"."'||tbl_salida||'") t
        WHERE t.row_num > 1 );
		CREATE INDEX z_'||tbl_salida||'_generaliced_geom_gidx
    ON "'|| esch ||'"."'||tbl_salida||'" USING gist
    (geom);
		
drop table "'|| esch ||'"."'|| nametable||'_union_area";
drop table "'|| esch ||'"."'|| nametable||'_tipo";
drop table "'|| esch ||'"."'|| nametable||'_dissolve";
drop table "'|| esch ||'"."'|| nametable||'_dissolve_simplify";
drop table "'|| esch ||'"."'|| nametable||'_dissolve_simplify2";
drop table "'|| esch ||'"."'|| nametable||'_dissolve_simplify_anillos";
drop table "'|| esch ||'"."'|| nametable||'_union_simply_anillos";
drop table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos";
drop table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump2";	
drop table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump";
drop table "'|| esch ||'"."'|| nametable||'_union";

ALTER TABLE "'|| esch ||'"."'||tbl_salida||'" ALTER COLUMN geom TYPE geometry(Geometry,'|| epsg ||') USING ST_SetSRID(geom,'|| epsg||');  
alter table "'|| esch ||'"."'||tbl_salida||'"  add column area double precision;
alter table "'|| esch ||'"."'||tbl_salida||'"  add column relation double precision;
update "'|| esch ||'"."'||tbl_salida||'" set area = st_area(geom);
update "'|| esch ||'"."'||tbl_salida||'" set relation = ((st_area(geom))/((ST_Perimeter(geom))^2))/0.079577471546  ;
create table "'|| esch ||'"."'||tbl_salida||'2" as select * from "'|| esch ||'"."'||tbl_salida||'" 
where area>'''|| min_area ||''' and relation > ('|| area_peri ||')^2;
drop table "'|| esch ||'"."'||tbl_salida||'";';

EXECUTE 'create table "'|| esch ||'"."'|| tbl_salida||'" as select  row_number ()over() as id_todo,* from "'|| esch ||'"."'||tbl_salida||'2";';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| tbl_salida ||'_geom_gidx
    ON "'|| esch ||'"."'|| tbl_salida||'" USING gist
    (geom);';
EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_union as select st_union (geom) from "'|| esch ||'"."'|| tbl_salida||'";';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_union_geom_gidx
    ON "'|| esch ||'".tmp'|| nametable ||'_union USING gist
    (st_union);';
EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_union_dump as select (st_dump (st_union)).geom from "'|| esch ||'".tmp'|| nametable ||'_union;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_union_dump_geom_gidx
    ON "'|| esch ||'".tmp'|| nametable ||'_union_dump USING gist
    (geom);';
EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_union_dump_boundary as select st_boundary(geom) as boundary from "'|| esch ||'".tmp'|| nametable ||'_union_dump;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_union_dump_boundary_geom_gidx
    ON "'|| esch ||'".tmp'|| nametable ||'_union_dump_boundary USING gist
    (boundary);';
EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_union_dump_exterior as select st_exteriorring(geom) as geom from "'|| esch ||'".tmp'|| nametable ||'_union_dump;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_union_dump_exterior_geom_gidx
    ON "'|| esch ||'".tmp'|| nametable ||'_union_dump_exterior USING gist
    (geom);';
EXECUTE 'create table  "'|| esch ||'".tmp'|| nametable ||'_union_dump_exterior_poligon as select ST_MakePolygon(geom) as geom from  "'|| esch ||'".tmp'|| nametable ||'_union_dump_exterior;';
EXECUTE 'create table  "'|| esch ||'".tmp'|| nametable ||'_union_dump_boundary_dump as select (ST_dump(boundary)).geom as geom from  "'|| esch ||'".tmp'|| nametable ||'_union_dump_boundary;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'__union_dump_exterior_poligon_geom_gidx
    ON "'|| esch ||'".tmp'|| nametable ||'_union_dump_exterior_poligon USING gist
    (geom);';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_union_ddump_boundary_dump_geom_gidx
    ON "'|| esch ||'".tmp'|| nametable ||'_union_dump_boundary_dump USING gist
    (geom);';

	
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_union_dump_contains;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_union_dump_contains as 
select a.geom as geom from  "'|| esch ||'".tmp'|| nametable ||'_union_dump_boundary_dump a,"'|| esch ||'".tmp'|| nametable ||'_union_dump_exterior_poligon b where ST_ContainsProperly (b.geom,a.geom);';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_union_dddump_contains_geom_gidx
    ON "'|| esch ||'".tmp'|| nametable ||'_union_dump_contains USING gist
    (geom);';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_union_huecos;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_union_huecos
as select st_makepolygon(geom) as geom from "'|| esch ||'".tmp'|| nametable ||'_union_dump_contains;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_huecos_id as select row_number ()over() as id_hueco,* from  "'|| esch ||'".tmp'|| nametable ||'_union_huecos;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_huecos_id_geom_gidx
    ON "'|| esch ||'".tmp'|| nametable ||'_huecos_id USING gist
    (geom);';
EXECUTE 'drop table if exists  "'|| esch ||'".tmp'|| nametable ||'_huecos_buffer_id;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_huecos_buffer_id
as select id_hueco,st_buffer (geom,0.05) as geom from "'|| esch ||'".tmp'|| nametable ||'_huecos_id;';

EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_huecos_buffer_id_geom_gidx
    ON "'|| esch ||'".tmp'|| nametable ||'_huecos_buffer_id USING gist
    (geom);';

	EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_huecos_intersection;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_huecos_intersection
 as select id_todo,id_hueco,st_area(st_intersection (a.geom,b.geom)) as area,a.geom from 
 (select * from "'|| esch ||'"."'|| tbl_salida||'") a, 
 "'|| esch ||'".tmp'|| nametable ||'_huecos_buffer_id b
 where st_intersects (a.geom,b.geom);';
 EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_dhuecos_intersection_geom_gidx
    ON "'|| esch ||'".tmp'|| nametable ||'_huecos_intersection USING gist
    (geom);';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_huecos_intersection_max;';
EXECUTE 'create table  "'|| esch ||'".tmp'|| nametable ||'_huecos_intersection_max as select id_hueco,max(area) 
from  "'|| esch ||'".tmp'|| nametable ||'_huecos_intersection group by id_hueco;';

EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_ori_huecos_id as select a.* from "'|| esch ||'".tmp'|| nametable ||'_huecos_intersection a,
"'|| esch ||'".tmp'|| nametable ||'_huecos_intersection_max b where a.area =b.max and a.id_hueco = b.id_hueco;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_ori_huecos_id_gidx
    ON "'|| esch ||'".tmp'|| nametable ||'_ori_huecos_id USING gist
    (geom);';
	
EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_id_hueco_id_todo as select  b.*,y.id_todo
from  "'|| esch ||'".tmp'|| nametable ||'_ori_huecos_id y
join "'|| esch ||'".tmp'|| nametable ||'_huecos_id b
on y.id_hueco = b.id_hueco;';

 EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_id_hueco_id_todo_gidx
    ON "'|| esch ||'".tmp'|| nametable ||'_id_hueco_id_todo USING gist
    (geom);';
	
EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_geom_hueco_geom_ori as 
select geom,id_todo from "'|| esch ||'"."'|| tbl_salida||'" 
union all select geom,id_todo
from  "'|| esch ||'".tmp'|| nametable ||'_id_hueco_id_todo;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_geom_hueco_geom_ori_gidx
    ON "'|| esch ||'".tmp'|| nametable ||'_geom_hueco_geom_ori USING gist
    (geom);';
	
EXECUTE 'create table "'|| esch ||'".tmp'|| nametable ||'_geom_hueco_geom_ori_unidas as select 
st_union(geom),id_todo from "'|| esch ||'".tmp'|| nametable ||'_geom_hueco_geom_ori group by id_todo;';

EXECUTE 'update "'|| esch ||'"."'|| tbl_salida||'" a set geom = b.st_union from 
"'|| esch ||'".tmp'|| nametable ||'_geom_hueco_geom_ori_unidas b
where a.id_todo = b.id_todo;';

EXECUTE 'drop table if exists "'|| esch ||'"."'||tbl_salida||'2";';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||';';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_union;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_union_dump;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_union_dump_boundary;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_union_dump_exterior;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_union_dump_exterior_poligon;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_union_dump_boundary_dump;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_union_dump_contains;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_union_huecos;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_huecos_id;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_huecos_buffer_id;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_huecos_intersection;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_huecos_intersection_max;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_ori_huecos_id;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_id_hueco_id_todo;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_geom_hueco_geom_ori;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nametable ||'_geom_hueco_geom_ori_unidas;';

END;
$BODY$;



CREATE OR REPLACE FUNCTION funciones.generalizar_areas(
	esch text,
	nametable text,
	tbl_salida text,
	tolerance double precision,
	simply double precision,
	pk_field character varying,
	min_area double precision,
	epsg integer)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: une los atributos iniciales a las capas generalizadas */
	EXECUTE 'create table "'|| esch ||'"."'|| nametable||'_tipo"

as select "'|| pk_field ||'", st_union (geom) as geom from "'|| esch ||'"."'|| nametable||'" group by "'|| pk_field ||'";

create table "'|| esch ||'"."'|| nametable||'_dissolve" as select "'|| pk_field ||'", (st_dump (geom)).geom as geom from "'|| esch ||'"."'|| nametable||'_tipo";
	
CREATE table "'|| esch ||'"."'|| nametable||'_dissolve_simplify"
	AS  
	SELECT "'|| pk_field ||'",
	st_simplifypreservetopology(geom, '|| simply ||')as geom
	
	FROM "'|| esch ||'"."'|| nametable||'_dissolve";
	CREATE TABLE "'|| esch ||'"."'|| nametable||'_dissolve_simplify2"
	as
	select fid, "'|| pk_field ||'",geom,st_area (geom) as area from (SELECT row_number() OVER () AS fid,
	 "'|| pk_field ||'",
	    ST_GeomFromText(ST_AsText( (ST_Dump(geom)).geom )) as geom
	    
	from "'|| esch ||'"."'|| nametable||'_dissolve_simplify")t;
	
CREATE TABLE "'|| esch ||'"."'|| nametable||'_dissolve_simplify_anillos"

AS
 SELECT "'|| nametable||'_dissolve_simplify2".fid,"'|| pk_field ||'",
    (st_dumprings(st_force2d("'|| nametable||'_dissolve_simplify2".geom))).geom AS geom,
    st_area((st_dumprings(st_force2d("'|| nametable||'_dissolve_simplify2".geom))).geom) AS area
   FROM "'|| esch ||'"."'|| nametable||'_dissolve_simplify2";
	

create table   "'|| esch ||'"."'|| nametable||'_union_simply_anillos" as SELECT fid ,
"'|| pk_field ||'",
   st_force2d(geom) as geom,
   area
FROM
  "'|| esch ||'"."'|| nametable||'_dissolve_simplify2"
UNION
SELECT fid ,
"'|| pk_field ||'",
   ST_Buffer(geom,'|| tolerance||'),
   area
FROM
  "'|| esch ||'"."'|| nametable||'_dissolve_simplify_anillos";
  
  UPDATE "'|| esch ||'"."'|| nametable||'_union_simply_anillos" SET geom=ST_makevalid(geom) WHERE NOT ST_isValid(geom);
    create table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos" as select st_union(geom) as geom, "'|| pk_field ||'" from  "'|| esch ||'"."'|| nametable||'_union_simply_anillos" group by "'|| pk_field ||'" 
	 ; create table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump2" as select (st_dump(geom)).geom as geom, "'|| pk_field ||'" from  "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos"  
;
create table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump" as SELECT row_number() OVER () AS fid,geom, "'|| pk_field ||'" from "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump2"
;create table "'|| esch ||'"."'|| nametable||'_union" as select fid, ST_CollectionExtract(st_union(geom),3) as geom, 
"'|| pk_field ||'",
st_area(ST_CollectionExtract(st_union(geom),3)) 
from "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump"  group by fid,"'|| pk_field ||'";

create table "'|| esch ||'"."'|| nametable||'_union_area" as SELECT * FROM "'|| esch ||'"."'|| nametable||'_union"
where st_area>'''|| min_area ||''';

CREATE INDEX z_'|| nametable||'_union_area_geom_gidx
    ON "'|| esch ||'"."'|| nametable||'_union_area" USING gist
    (geom);
	
	
	create table "'|| esch ||'"."'||tbl_salida||'" as select a.*, b.geom as geom2 
	from "'|| esch ||'"."'|| nametable||'" a join
	"'|| esch ||'"."'|| nametable||'_union_area" b on a."'|| pk_field ||'" = b."'|| pk_field ||'" and st_intersects (st_setsrid(b.geom,'|| epsg||'),a.geom);
		alter table "'|| esch ||'"."'||tbl_salida||'"  drop geom;
	alter table "'|| esch ||'"."'||tbl_salida||'"  rename geom2 to geom;
	alter table "'|| esch ||'"."'||tbl_salida||'"  add column ida serial;

DELETE FROM  "'|| esch ||'"."'||tbl_salida||'"
WHERE ida IN
    (SELECT ida
    FROM 
        (SELECT ida,
         ROW_NUMBER() OVER( PARTITION BY geom
        ORDER BY  ida ) AS row_num
        FROM "'|| esch ||'"."'||tbl_salida||'") t
        WHERE t.row_num > 1 );
		CREATE INDEX z_'||tbl_salida||'_generaliced_geom_gidx
    ON "'|| esch ||'"."'||tbl_salida||'" USING gist
    (geom);
	
drop table "'|| esch ||'"."'|| nametable||'_union_area";
drop table "'|| esch ||'"."'|| nametable||'_tipo";
drop table "'|| esch ||'"."'|| nametable||'_dissolve";
drop table "'|| esch ||'"."'|| nametable||'_dissolve_simplify";
drop table "'|| esch ||'"."'|| nametable||'_dissolve_simplify2";
drop table "'|| esch ||'"."'|| nametable||'_dissolve_simplify_anillos";
drop table "'|| esch ||'"."'|| nametable||'_union_simply_anillos";
drop table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos";
drop table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump2";	
drop table "'|| esch ||'"."'|| nametable||'_union_simply_anillos_unidos_dump";
drop table "'|| esch ||'"."'|| nametable||'_union";

ALTER TABLE "'|| esch ||'"."'||tbl_salida||'" ALTER COLUMN geom TYPE geometry(Geometry,'|| epsg ||') USING ST_SetSRID(geom,'|| epsg||');  ';
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.nineintersection_matrix_mv(
	validar boolean,
	esch text,
	nameview text,
	name_table1 text,
	name_table2 text,
	matriz text)
    RETURNS text
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ declare geome1 character varying;
			declare	geome2 character varying;
			declare tab text;
BEGIN

	/*Descripcion: crea una vista o vista materializada que comprueba la matriz 9d y extrae si es cierto su intersección si no hay correspondencia retorna vista vacia*/
EXECUTE 'alter table "'|| esch ||'"."'|| name_table1 ||'" rename to __nmtab1';
EXECUTE 'alter table "'|| esch ||'"."'|| name_table2 ||'" rename to __nmtab2';
SELECT f_geometry_column into geome1 FROM geometry_columns  WHERE  f_table_name = '__nmtab1';
SELECT f_geometry_column into geome2 FROM geometry_columns  WHERE  f_table_name = '__nmtab2';
EXECUTE 'alter table "'|| esch ||'".__nmtab1 rename to "'|| name_table1 ||'"';
EXECUTE 'alter table "'|| esch ||'".__nmtab2 rename to "'|| name_table2 ||'"';

CASE
  WHEN validar = '0' THEN
EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as 
select  ROW_NUMBER() OVER () as  fid_'|| name_table1 ||',* from (SELECT st_intersection(a.'|| geome1 ||',b.'|| geome2 ||') as geom
from "'|| esch ||'"."'|| name_table1 ||'" a, "'|| esch ||'"."'|| name_table2 ||'" b
where ST_Relate(a.'|| geome1 ||',b.'|| geome2 ||', '''|| matriz ||''') = true)re';
EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" ( fid_'|| name_table1 ||')';
ELSE tab := 'geom1';
  END CASE;
  CASE
  WHEN validar = '1' THEN
  EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as 
select  ROW_NUMBER() OVER () as fid_'|| name_table1 ||',* from (SELECT a.'|| geome1 ||',count(a.fid)as numgeomtouch
from "'|| esch ||'"."'|| name_table1 ||'" a, "'|| esch ||'"."'|| name_table2 ||'" b
where ST_Relate(a.'|| geome1 ||',b.'|| geome2 ||', '''|| matriz ||''') = true group by a.geom,fid)re';
EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" ( fid_'|| name_table1 ||')';
ELSE tab := 'intersectiongeom';
END CASE;
Return tab;
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.nineintersection_matrix_vw(
	validar boolean,
	esch text,
	nameview text,
	name_table1 text,
	name_table2 text,
	matriz text)
    RETURNS text
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ declare geome1 character varying;
			declare	geome2 character varying;
			declare tab text;
BEGIN

	/*Descripcion: crea una vista o vista materializada que comprueba la matriz 9d y extrae si es cierto su intersección si no hay correspondencia retorna vista vacia*/
EXECUTE 'alter table "'|| esch ||'"."'|| name_table1 ||'" rename to __nmtab1';
EXECUTE 'alter table "'|| esch ||'"."'|| name_table2 ||'" rename to __nmtab2';
SELECT f_geometry_column into geome1 FROM geometry_columns  WHERE  f_table_name = '__nmtab1';
SELECT f_geometry_column into geome2 FROM geometry_columns  WHERE  f_table_name = '__nmtab2';
EXECUTE 'alter table "'|| esch ||'".__nmtab1 rename to "'|| name_table1 ||'"';
EXECUTE 'alter table "'|| esch ||'".__nmtab2 rename to "'|| name_table2 ||'"';

CASE
  WHEN validar = '0' THEN
EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as 
select  ROW_NUMBER() OVER (ORDER BY fid_'|| name_table1 ||' ) as fid,* from (SELECT a.fid as fid_'|| name_table1 ||',b.fid as fid_'|| name_table2 ||',st_intersection(a.'|| geome1 ||',b.'|| geome2 ||')
from "'|| esch ||'"."'|| name_table1 ||'" a, "'|| esch ||'"."'|| name_table2 ||'" b
where ST_Relate(a.'|| geome1 ||',b.'|| geome2 ||', '''|| matriz ||''') = true)re';
ELSE tab := 'geom1';
  END CASE;
  CASE
  WHEN validar = '1' THEN
  EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as 
select  ROW_NUMBER() OVER (ORDER BY fid_'|| name_table1 ||' ) as fid,* from (SELECT a.fid as fid_'|| name_table1 ||',a.'|| geome1 ||',count(a.fid)as numgeomtouch
from "'|| esch ||'"."'|| name_table1 ||'" a, "'|| esch ||'"."'|| name_table2 ||'" b
where ST_Relate(a.'|| geome1 ||',b.'|| geome2 ||', '''|| matriz ||''') = true group by a.geom,fid_'|| name_table1 ||')re';
ELSE tab := 'intersectiongeom';
END CASE;
Return tab;
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.rellenar_huecos_mv(
	esch text,
	nametable text,
	nameview text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
   EXECUTE 'CREATE MATERIALIZED VIEW "'|| esch ||'"."'||nameview||'"
 AS
 SELECT st_union(st_makevalid(pol3.geom)) AS geom,
    pol3.layer_ori
   FROM ( SELECT re.fid,
            re.geom,
            re.layer_ori
           FROM ( SELECT pol3_1.bgeom,
                    pol3_1.id AS fid,
                    pol6.layer_ori,
                    pol6.area,
                    pol6.geom
                   FROM ( SELECT ra.bgeom,
                            max(ra.area) AS area,
                            ra.id
                           FROM ( SELECT re_1.bgeom,
                                    re_1.layer_ori,
                                    re_1.id,
                                    re_1.area,
                                    re_1.geom
                                   FROM ( SELECT st_area(st_intersection(pol3_2.bgeom, st_makevalid(pol6_1.geom))) AS area,
    pol6_1.layer_ori,
    pol3_2.id,
    pol3_2.bgeom,
    pol3_2.geom
   FROM ( SELECT DISTINCT ON ((st_makepolygon(ra_1.geom))) row_number() OVER () AS id,
      st_buffer(ra_1.geom, 0.00005::double precision, ''endcap=flat join=round''::text) AS bgeom,
      st_makepolygon(ra_1.geom) AS geom,
      ra_1.layer_ori
     FROM ( SELECT re_2.geom,
        re_2.contiene,
        st_touches(st_buffer(st_force2d(st_makevalid(a.geom)), 0::double precision), re_2.geom) AS st_touches,
        st_buffer(st_force2d(st_makevalid(a.geom)), 0::double precision) AS st_buffer,
        a.layer_ori
       FROM "'|| esch ||'"."'|| nametable||'" a,
        ( SELECT st_contains((st_dump(st_boundary(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision)))))).geom, st_exteriorring(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision))))) AS contiene,
          (st_dump(st_boundary(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision)))))).geom AS geom
         FROM "'|| esch ||'"."'|| nametable||'") re_2) ra_1
    WHERE ra_1.contiene = ''false'' AND ra_1.st_touches = ''true'') pol3_2,
    "'|| esch ||'"."'|| nametable||'" pol6_1
  WHERE st_intersects(pol3_2.bgeom, st_makevalid(pol6_1.geom))) re_1) ra
                          GROUP BY ra.id, ra.bgeom) pol3_1
                     LEFT JOIN ( SELECT re_1.bgeom,
                            re_1.layer_ori,
                            re_1.id,
                            re_1.area,
                            re_1.geom
                           FROM ( SELECT st_area(st_intersection(pol3_2.bgeom, st_makevalid(pol6_1.geom))) AS area,
                                    pol6_1.layer_ori,
                                    pol3_2.id,
                                    pol3_2.bgeom,
                                    pol3_2.geom
                                   FROM ( SELECT DISTINCT ON ((st_makepolygon(ra.geom))) row_number() OVER () AS id,
    st_buffer(ra.geom, 0.00005::double precision, ''endcap=flat join=round''::text) AS bgeom,
    st_makepolygon(ra.geom) AS geom,
    ra.layer_ori
   FROM ( SELECT re_2.geom,
      re_2.contiene,
      st_touches(st_buffer(st_force2d(st_makevalid(a.geom)), 0::double precision), re_2.geom) AS st_touches,
      st_buffer(st_force2d(st_makevalid(a.geom)), 0::double precision) AS st_buffer,
      a.layer_ori
     FROM "'|| esch ||'"."'|| nametable||'" a,
      ( SELECT st_contains((st_dump(st_boundary(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision)))))).geom, st_exteriorring(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision))))) AS contiene,
        (st_dump(st_boundary(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision)))))).geom AS geom
       FROM "'|| esch ||'"."'|| nametable||'") re_2) ra
  WHERE ra.contiene = ''false'' AND ra.st_touches = ''true'') pol3_2,
                                    "'|| esch ||'"."'|| nametable||'" pol6_1
                                  WHERE st_intersects(pol3_2.bgeom, st_makevalid(pol6_1.geom))) re_1) pol6 ON pol3_1.area = pol6.area) re
        UNION ALL
         SELECT "'|| nametable||'".fid,
            "'|| nametable||'".geom,
            "'|| nametable||'".layer_ori
           FROM "'|| esch ||'"."'|| nametable||'") pol3
  GROUP BY pol3.layer_ori;';
  END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.rellenar_huecos_tbl2(
	esch text,
	nameview text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN

EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||';';
EXECUTE 'create table  "'|| esch ||'".tmp'|| nameview ||' as select st_makevalid (geom) as geom, layer_ori from "'|| esch ||'"."'|| nameview ||'";';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||' USING gist
    (geom);';
EXECUTE 'drop table if exists "'|| esch ||'"."'|| nametable||'";';
EXECUTE 'create table "'|| esch ||'"."'|| nametable||'" as select  row_number ()over() as id_todo,* from "'|| esch ||'".tmp'|| nameview ||';';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_geom_gidx
    ON "'|| esch ||'"."'|| nametable||'" USING gist
    (geom);';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union as select st_union (geom) from "'|| esch ||'".tmp'|| nameview ||';';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union USING gist
    (st_union);';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump as select (st_dump (st_union)).geom from "'|| esch ||'".tmp'|| nameview ||'_union;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union_dump USING gist
    (geom);';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary as select st_boundary(geom) as boundary from "'|| esch ||'".tmp'|| nameview ||'_union_dump;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_boundary_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary USING gist
    (boundary);';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior as select st_exteriorring(geom) as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_exterior_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior USING gist
    (geom);';
EXECUTE 'create table  "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon as select ST_MakePolygon(geom) as geom from  "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior;';
EXECUTE 'create table  "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump as select (ST_dump(boundary)).geom as geom from  "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'__union_dump_exterior_poligon_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon USING gist
    (geom);';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_boundary_dump_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump USING gist
    (geom);';

	
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains as 
select a.geom as geom from  "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump a,"'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon b where ST_ContainsProperly (b.geom,a.geom);';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_contains_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains USING gist
    (geom);';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_huecos;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_huecos
as select st_makepolygon(geom) as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_id as select row_number ()over() as id_hueco,* from  "'|| esch ||'".tmp'|| nameview ||'_union_huecos;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_huecos_id_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_huecos_id USING gist
    (geom);';
EXECUTE 'drop table if exists  "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id
as select id_hueco,st_buffer (geom,0.05) as geom from "'|| esch ||'".tmp'|| nameview ||'_huecos_id;';

EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_huecos_buffer_id_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id USING gist
    (geom);';

	EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection
 as select id_todo,id_hueco,st_area(st_intersection (a.geom,b.geom)) as area,a.geom, layer_ori from "'|| esch ||'"."'|| nametable||'" a, 
 "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id b
 where st_intersects (a.geom,b.geom);';
 EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_huecos_intersection_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection USING gist
    (geom);';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max;';
EXECUTE 'create table  "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max as select id_hueco,max(area) 
from  "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection group by id_hueco;';

EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id as select a.* from "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection a,
"'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max b where a.area =b.max and a.id_hueco = b.id_hueco;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_ohuecos_ori_geom as select id_hueco,geom from  "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id
union all select id_hueco,geom from "'|| esch ||'".tmp'|| nameview ||'_huecos_id;';
 EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_ohuecos_ori_geom_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_ohuecos_ori_geom USING gist
    (geom);';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_centroid as select  id_todo,id_hueco, st_pointonsurface(geom) as geom from "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection;';	
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_ori as select id_hueco,st_union(geom) as geom from "'|| esch ||'".tmp'|| nameview ||'_ohuecos_ori_geom group by id_hueco;';
 EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_huecos_intersection_centroid_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_centroid USING gist
    (geom);';
	 EXECUTE 'create INDEX '|| esch ||'tmp'|| nameview ||'_huecos_ori_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_huecos_ori USING gist
    (geom);';
		
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_id_ori as  select a.geom,b.id_todo from "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_centroid b, 
"'|| esch ||'".tmp'|| nameview ||'_huecos_ori a where st_intersects (a.geom,b.geom) and a.id_hueco = b.id_hueco;';
EXECUTE 'create INDEX '|| esch ||'tmp'|| nameview ||'_huecos_id_ori_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_huecos_id_ori USING gist
    (geom);';
EXECUTE 'create table  "'|| esch ||'".tmp'|| nameview ||'_huecos_id_ori_union as select 
st_union (geom),id_todo from "'|| esch ||'".tmp'|| nameview ||'_huecos_id_ori group by id_todo;';
		
EXECUTE 'update "'|| esch ||'"."'|| nametable||'" a set geom = b.st_union from 
"'|| esch ||'".tmp'|| nameview ||'_huecos_id_ori_union b
where a.id_todo = b.id_todo;';

EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||';';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_huecos;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_id;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_ohuecos_ori_geom;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_centroid;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_ori;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_id_ori;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_id_ori_union;';

END;
$BODY$;



CREATE OR REPLACE FUNCTION funciones.rellenar_huecos_tbl(
	esch text,
	nameview text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN

EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||';';
EXECUTE 'create table  "'|| esch ||'".tmp'|| nameview ||' as select st_makevalid (geom) as geom, layer_ori from "'|| esch ||'"."'|| nameview ||'";';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||' USING gist
    (geom);';
EXECUTE 'drop table if exists "'|| esch ||'"."'|| nametable||'";';
EXECUTE 'create table "'|| esch ||'"."'|| nametable||'" as select  row_number ()over() as id_todo,* from "'|| esch ||'".tmp'|| nameview ||';';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_geom_gidx
    ON "'|| esch ||'"."'|| nametable||'" USING gist
    (geom);';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union as select st_union (geom) from "'|| esch ||'".tmp'|| nameview ||';';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union USING gist
    (st_union);';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump as select (st_dump (st_union)).geom from "'|| esch ||'".tmp'|| nameview ||'_union;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union_dump USING gist
    (geom);';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary as select st_boundary(geom) as boundary from "'|| esch ||'".tmp'|| nameview ||'_union_dump;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_boundary_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary USING gist
    (boundary);';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior as select st_exteriorring(geom) as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_exterior_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior USING gist
    (geom);';
EXECUTE 'create table  "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon as select ST_MakePolygon(geom) as geom from  "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior;';
EXECUTE 'create table  "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump as select (ST_dump(boundary)).geom as geom from  "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'__union_dump_exterior_poligon_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon USING gist
    (geom);';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_boundary_dump_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump USING gist
    (geom);';

	
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains as 
select a.geom as geom from  "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump a,"'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon b where ST_ContainsProperly (b.geom,a.geom);';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_contains_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains USING gist
    (geom);';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_huecos;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_huecos
as select st_makepolygon(geom) as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_id as select row_number ()over() as id_hueco,* from  "'|| esch ||'".tmp'|| nameview ||'_union_huecos;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_huecos_id_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_huecos_id USING gist
    (geom);';
EXECUTE 'drop table if exists  "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id
as select id_hueco,st_buffer (geom,0.05) as geom from "'|| esch ||'".tmp'|| nameview ||'_huecos_id;';

EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_huecos_buffer_id_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id USING gist
    (geom);';

	EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection
 as select id_todo,id_hueco,st_area(st_intersection (a.geom,b.geom)) as area,a.geom, layer_ori from 
 (select * from "'|| esch ||'"."'|| nametable||'" where layer_ori = ''cub'') a, 
 "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id b
 where st_intersects (a.geom,b.geom);';
 EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_huecos_intersection_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection USING gist
    (geom);';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max;';
EXECUTE 'create table  "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max as select id_hueco,max(area) 
from  "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection group by id_hueco;';

EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id as select a.* from "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection a,
"'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max b where a.area =b.max and a.id_hueco = b.id_hueco;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_ori_huecos_id_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id USING gist
    (geom);';
	
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_id_hueco_id_todo as select  b.*,y.id_todo
from  "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id y
join "'|| esch ||'".tmp'|| nameview ||'_huecos_id b
on y.id_hueco = b.id_hueco;';

 EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_id_hueco_id_todo_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_id_hueco_id_todo USING gist
    (geom);';
	
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori as 
select geom,id_todo from "'|| esch ||'"."'|| nametable||'" 
union all select geom,id_todo
from  "'|| esch ||'".tmp'|| nameview ||'_id_hueco_id_todo;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_geom_hueco_geom_ori_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori USING gist
    (geom);';
	
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori_unidas as select 
st_union(geom),id_todo from "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori group by id_todo;';

EXECUTE 'update "'|| esch ||'"."'|| nametable||'" a set geom = b.st_union from 
"'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori_unidas b
where a.id_todo = b.id_todo;';

EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||';';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_huecos;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_id;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_id_hueco_id_todo;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori_unidas;';

END;
$BODY$;


CREATE OR REPLACE FUNCTION funciones.rellenar_huecos_tbl_todo(
	esch text,
	nameview text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN

EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||';';
EXECUTE 'create table  "'|| esch ||'".tmp'|| nameview ||' as select st_makevalid (geom) as geom, layer_ori from "'|| esch ||'"."'|| nameview ||'";';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||' USING gist
    (geom);';
EXECUTE 'drop table if exists "'|| esch ||'"."'|| nametable||'";';
EXECUTE 'create table "'|| esch ||'"."'|| nametable||'" as select  row_number ()over() as id_todo,* from "'|| esch ||'".tmp'|| nameview ||';';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nametable ||'_geom_gidx
    ON "'|| esch ||'"."'|| nametable||'" USING gist
    (geom);';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union as select st_union (geom) from "'|| esch ||'".tmp'|| nameview ||';';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union USING gist
    (st_union);';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump as select (st_dump (st_union)).geom from "'|| esch ||'".tmp'|| nameview ||'_union;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union_dump USING gist
    (geom);';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary as select st_boundary(geom) as boundary from "'|| esch ||'".tmp'|| nameview ||'_union_dump;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_boundary_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary USING gist
    (boundary);';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior as select st_exteriorring(geom) as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_exterior_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior USING gist
    (geom);';
EXECUTE 'create table  "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon as select ST_MakePolygon(geom) as geom from  "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior;';
EXECUTE 'create table  "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump as select (ST_dump(boundary)).geom as geom from  "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'__union_dump_exterior_poligon_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon USING gist
    (geom);';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_boundary_dump_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump USING gist
    (geom);';

	
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains as 
select a.geom as geom from  "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump a,"'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon b where ST_ContainsProperly (b.geom,a.geom);';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_union_dump_contains_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains USING gist
    (geom);';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_huecos;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_union_huecos
as select st_makepolygon(geom) as geom from "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_id as select row_number ()over() as id_hueco,* from  "'|| esch ||'".tmp'|| nameview ||'_union_huecos;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_huecos_id_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_huecos_id USING gist
    (geom);';
EXECUTE 'drop table if exists  "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id
as select id_hueco,st_buffer (geom,0.05) as geom from "'|| esch ||'".tmp'|| nameview ||'_huecos_id;';

EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_huecos_buffer_id_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id USING gist
    (geom);';

	EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection;';
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection
 as select id_todo,id_hueco,st_area(st_intersection (a.geom,b.geom)) as area,a.geom, layer_ori from "'|| esch ||'"."'|| nametable||'" a, 
 "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id b
 where st_intersects (a.geom,b.geom);';
 EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_huecos_intersection_geom_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection USING gist
    (geom);';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max;';
EXECUTE 'create table  "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max as select id_hueco,max(area) 
from  "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection group by id_hueco;';

EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id as select a.* from "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection a,
"'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max b where a.area =b.max and a.id_hueco = b.id_hueco;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_ori_huecos_id_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id USING gist
    (geom);';
	
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_id_hueco_id_todo as select  b.*,y.id_todo
from  "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id y
join "'|| esch ||'".tmp'|| nameview ||'_huecos_id b
on y.id_hueco = b.id_hueco;';

 EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_id_hueco_id_todo_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_id_hueco_id_todo USING gist
    (geom);';
	
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori as 
select geom,id_todo from "'|| esch ||'"."'|| nametable||'" 
union all select geom,id_todo
from  "'|| esch ||'".tmp'|| nameview ||'_id_hueco_id_todo;';
EXECUTE 'create INDEX '|| esch ||'_tmp'|| nameview ||'_geom_hueco_geom_ori_gidx
    ON "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori USING gist
    (geom);';
	
EXECUTE 'create table "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori_unidas as select 
st_union(geom),id_todo from "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori group by id_todo;';

EXECUTE 'update "'|| esch ||'"."'|| nametable||'" a set geom = b.st_union from 
"'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori_unidas b
where a.id_todo = b.id_todo;';

EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||';';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_exterior_poligon;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_boundary_dump;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_dump_contains;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_union_huecos;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_id;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_buffer_id;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_huecos_intersection_max;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_ori_huecos_id;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_id_hueco_id_todo;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori;';
EXECUTE 'drop table if exists "'|| esch ||'".tmp'|| nameview ||'_geom_hueco_geom_ori_unidas;';

END;
$BODY$;



CREATE OR REPLACE FUNCTION funciones.rellenar_huecos_vw(
	esch text,
	nametable text,
	nameview text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
   EXECUTE 'CREATE OR REPLACE VIEW "'|| esch ||'"."'||nameview||'"
 AS
 SELECT st_union(st_makevalid(pol3.geom)) AS geom,
    pol3.layer_ori
   FROM ( SELECT re.fid,
            re.geom,
            re.layer_ori
           FROM ( SELECT pol3_1.bgeom,
                    pol3_1.id AS fid,
                    pol6.layer_ori,
                    pol6.area,
                    pol6.geom
                   FROM ( SELECT ra.bgeom,
                            max(ra.area) AS area,
                            ra.id
                           FROM ( SELECT re_1.bgeom,
                                    re_1.layer_ori,
                                    re_1.id,
                                    re_1.area,
                                    re_1.geom
                                   FROM ( SELECT st_area(st_intersection(pol3_2.bgeom, st_makevalid(pol6_1.geom))) AS area,
    pol6_1.layer_ori,
    pol3_2.id,
    pol3_2.bgeom,
    pol3_2.geom
   FROM ( SELECT DISTINCT ON ((st_makepolygon(ra_1.geom))) row_number() OVER () AS id,
      st_buffer(ra_1.geom, 0.00005::double precision, ''endcap=flat join=round''::text) AS bgeom,
      st_makepolygon(ra_1.geom) AS geom,
      ra_1.layer_ori
     FROM ( SELECT re_2.geom,
        re_2.contiene,
        st_touches(st_buffer(st_force2d(st_makevalid(a.geom)), 0::double precision), re_2.geom) AS st_touches,
        st_buffer(st_force2d(st_makevalid(a.geom)), 0::double precision) AS st_buffer,
        a.layer_ori
       FROM "'|| esch ||'"."'|| nametable||'" a,
        ( SELECT st_contains((st_dump(st_boundary(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision)))))).geom, st_exteriorring(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision))))) AS contiene,
          (st_dump(st_boundary(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision)))))).geom AS geom
         FROM "'|| esch ||'"."'|| nametable||'") re_2) ra_1
    WHERE ra_1.contiene = ''false'' AND ra_1.st_touches = ''true'') pol3_2,
    "'|| esch ||'"."'|| nametable||'" pol6_1
  WHERE st_intersects(pol3_2.bgeom, st_makevalid(pol6_1.geom))) re_1) ra
                          GROUP BY ra.id, ra.bgeom) pol3_1
                     LEFT JOIN ( SELECT re_1.bgeom,
                            re_1.layer_ori,
                            re_1.id,
                            re_1.area,
                            re_1.geom
                           FROM ( SELECT st_area(st_intersection(pol3_2.bgeom, st_makevalid(pol6_1.geom))) AS area,
                                    pol6_1.layer_ori,
                                    pol3_2.id,
                                    pol3_2.bgeom,
                                    pol3_2.geom
                                   FROM ( SELECT DISTINCT ON ((st_makepolygon(ra.geom))) row_number() OVER () AS id,
    st_buffer(ra.geom, 0.00005::double precision, ''endcap=flat join=round''::text) AS bgeom,
    st_makepolygon(ra.geom) AS geom,
    ra.layer_ori
   FROM ( SELECT re_2.geom,
      re_2.contiene,
      st_touches(st_buffer(st_force2d(st_makevalid(a.geom)), 0::double precision), re_2.geom) AS st_touches,
      st_buffer(st_force2d(st_makevalid(a.geom)), 0::double precision) AS st_buffer,
      a.layer_ori
     FROM "'|| esch ||'"."'|| nametable||'" a,
      ( SELECT st_contains((st_dump(st_boundary(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision)))))).geom, st_exteriorring(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision))))) AS contiene,
        (st_dump(st_boundary(st_union(st_multi(st_buffer(st_force2d("'|| nametable||'".geom), 0::double precision)))))).geom AS geom
       FROM "'|| esch ||'"."'|| nametable||'") re_2) ra
  WHERE ra.contiene = ''false'' AND ra.st_touches = ''true'') pol3_2,
                                    "'|| esch ||'"."'|| nametable||'" pol6_1
                                  WHERE st_intersects(pol3_2.bgeom, st_makevalid(pol6_1.geom))) re_1) pol6 ON pol3_1.area = pol6.area) re
        UNION ALL
         SELECT "'|| nametable||'".fid,
            "'|| nametable||'".geom,
            "'|| nametable||'".layer_ori
           FROM "'|| esch ||'"."'|| nametable||'") pol3
  GROUP BY pol3.layer_ori;';
  END;
$BODY$;


CREATE OR REPLACE FUNCTION funciones.renombrar_multiples_tablas(
	_schema text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
DECLARE
    row record;
BEGIN
    FOR row IN 
        SELECT
            table_name,
			table_schema
        FROM
             information_schema.tables 
        where
            table_schema = ''||_schema || ''
    LOOP
        EXECUTE 'Alter TABLE "'||row.table_schema||'"."'||row.table_name||'" rename to "'||row.table_name||'_ft"';
        EXECUTE 'create table TABLE "'||row.table_schema||'"."'||row.table_name||'" as select * from    "'||row.table_schema||'"."'||row.table_name||'_ft"';
    END LOOP;
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.sacar_puntos_erroneos_mv2(
	esch text,
	nameview text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ BEGIN
EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'" as (select distinct on (ra.geom)  st_pointonsurface (ra.geom) as geom,  ra.fid, 
  ''hollow space'' as errores from 
(select  re.geom, re.contiene, ST_touches (ST_Buffer(st_force2d(st_makevalid(a.geom)),0),re.geom), ST_Buffer(st_force2d(st_makevalid(a.geom)),0),fid 
 FROM "'|| esch ||'"."'|| nametable||'" a, (SELECT  st_contains (((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom),
		ST_ExteriorRing(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0))))) as contiene, 
		((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom)  AS geom
	   FROM "'|| esch ||'"."'|| nametable||'") re) ra
 where ra.contiene = ''false'' and ra.st_touches = ''true'')
UNION ALL
(SELECT re.re as geom, re.fid, ra.ra as errores
   FROM (SELECT ST_SetSRID(ST_MakePoint (a[2]::double precision,  a[4]::double precision),4326) as re, fid 
		 from  
		(select regexp_split_to_array( to_tsvector(ST_IsValidReason(st_force2d(geom)))::text,''''''''), fid FROM "'|| esch ||'"."'|| nametable||'" WHERE NOT ST_IsValid(geom))as dt(a)) re, 
		(select ST_IsValidReason(geom) as ra, fid  fROM "'|| esch ||'"."'|| nametable||'" WHERE NOT ST_IsValid(geom)) ra where ra.fid = re.fid)';
   
	END;
$BODY$;


CREATE OR REPLACE FUNCTION funciones.sacar_puntos_erroneos_mv(
	esch text,
	nameview text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ BEGIN
	/*Descripcion: Saca errores topológicos */

Execute 'create materialized view "'|| esch ||'"."'|| nameview ||'" as select row_number() OVER () AS id'|| nametable||', id, error,geom from( 
select gid1 as id,concat(''geometries with union points'','':'','' id '',gid1,'' y '',gid2) as "error", seudogeom as geom from 
(select c1.id as gid1, max (c2.id) as gid2, c1.geom as seudogeom,
count (*) as num from  (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") c1, 
 (select (st_dumppoints(geom)).geom as geoml, geom, id from "'|| esch ||'"."'|| nametable ||'") c2 where c1.id<>c2.id
and c1.geom && c2.geom and (st_distance (c1.geom,c2.geoml))=0 
group by c1.id,c1.geom)as tabla where num = 1
								  

union all

select id1 as id,''Junctions without union'' as errores, geom from (select r1.id as id1, r2.id as id2, r1.geom from (select a.id,(st_dumppoints(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0)))).geom as geom
from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b 
where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.id>b.id) r1
left join (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") r2
on st_distance (r1.geom,r2.geom) = 0) as tabla where id2 is null 

union all

select id1 as id,''Points without union'' as errores, point as geom from(select r1.geom as point, r1.id as id1, r2.id as id2
from (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") r1 
	  left join "'|| esch ||'"."'|| nametable ||'" r2 
on r1.id<>r2.id and st_expand (r1.geom,1000) && r2.geom and st_distance (r1.geom,r2.geom) = 0)
as tabla where id2 is null

union all

select distinct on (ra.geom)  ra.id, 
  ''Hollow space'' as errores,st_centroid (ra.geom) as geom from 
(select  re.geom, re.contiene, ST_touches (ST_Buffer(st_force2d(st_makevalid(a.geom)),0),re.geom), ST_Buffer(st_force2d(st_makevalid(a.geom)),0),id 
 FROM "'|| esch ||'"."'|| nametable ||'" a, (SELECT  st_contains (((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom),
		ST_ExteriorRing((st_dump(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0))))).geom)) as contiene, 
		((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom)  AS geom
	   FROM "'|| esch ||'"."'|| nametable ||'") re) ra
 where ra.contiene is null and ra.st_touches = ''true''

union all

select gida as id,''Isolated geometry'' as "error",ST_PointOnSurface(st_makepolygon((st_dump(ST_CollectionExtract(contorno,2))).geom)) from (select t1.id as gida, st_multi (st_boundary(t1.geom)) as contorno, t2.id as gidb
from "'|| esch ||'"."'|| nametable ||'" t1 left join "'|| esch ||'"."'|| nametable ||'" t2
on t1.id<>t2.id and st_intersects (t1.geom,t2.geom)) as tabla where gidb is null

union all

select id,''Repeated vertex'' as "error", ST_PointOnSurface(geomp) as geom 
from (select *, (st_dumppoints (geom)).geom as geomp from "'|| esch ||'"."'|| nametable ||'" 
where not st_asewkb(st_removerepeatedpoints (geom)) = st_asewkb (geom))r
GROUP BY id,geomp
     HAVING COUNT(*)>1
	 
union all

select a.id,concat(''
duplicate geometry'','':'','' id original '',b.id) as "error", ST_PointOnSurface(a.geom) as geom
from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b 
where a.id>b.id and ST_Equals(ST_SnapToGrid(a.geom, 0.005), ST_SnapToGrid(b.geom, 0.005))

union all

select a.id,concat(''overlapping geometries'','':'','' id '',a.id,'' y '',b.id) as "error",ST_PointOnSurface(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0))) as geom
from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b 
where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.id>b.id)re';
EXECUTE 'create unique index on "'|| esch ||'"."'|| nameview ||'" (id'|| nametable||')';
END;
$BODY$;

CREATE OR REPLACE FUNCTION funciones.sacar_puntos_erroneos_vw2(
	esch text,
	nameview text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ BEGIN
EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'" as (select distinct on (ra.geom)  st_pointonsurface (ra.geom) as geom,  ra.fid, 
  ''hollow space'' as errores from 
(select  re.geom, re.contiene, ST_touches (ST_Buffer(st_force2d(st_makevalid(a.geom)),0),re.geom), ST_Buffer(st_force2d(st_makevalid(a.geom)),0),fid 
 FROM "'|| esch ||'"."'|| nametable||'" a, (SELECT  st_contains (((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom),
		ST_ExteriorRing(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0))))) as contiene, 
		((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom)  AS geom
	   FROM "'|| esch ||'"."'|| nametable||'") re) ra
 where ra.contiene = ''false'' and ra.st_touches = ''true'')
UNION ALL
(SELECT re.re as geom, re.fid, ra.ra as errores
   FROM (SELECT ST_SetSRID(ST_MakePoint (a[2]::double precision,  a[4]::double precision),4326) as re, fid 
		 from  
		(select regexp_split_to_array( to_tsvector(ST_IsValidReason(st_force2d(geom)))::text,''''''''), fid FROM "'|| esch ||'"."'|| nametable||'" WHERE NOT ST_IsValid(geom))as dt(a)) re, 
		(select ST_IsValidReason(geom) as ra, fid  fROM "'|| esch ||'"."'|| nametable||'" WHERE NOT ST_IsValid(geom)) ra where ra.fid = re.fid)';
   
	END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.sacar_puntos_erroneos_vw(
	esch text,
	nameview text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ BEGIN
	/*Descripcion: Saca errores topológicos */

Execute 'create view "'|| esch ||'"."'|| nameview ||'" as select row_number() OVER () AS id'|| nametable||', id, error,geom from( 
select gid1 as id,concat(''geometries with union points'','':'','' id '',gid1,'' y '',gid2) as "error", seudogeom as geom from 
(select c1.id as gid1, max (c2.id) as gid2, c1.geom as seudogeom,
count (*) as num from  (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") c1, 
 (select (st_dumppoints(geom)).geom as geoml, geom, id from "'|| esch ||'"."'|| nametable ||'") c2 where c1.id<>c2.id
and c1.geom && c2.geom and (st_distance (c1.geom,c2.geoml))=0 
group by c1.id,c1.geom)as tabla where num = 1
								  

union all

select id1 as id,''Junctions without union'' as errores, geom from (select r1.id as id1, r2.id as id2, r1.geom from (select a.id,(st_dumppoints(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0)))).geom as geom
from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b 
where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.id>b.id) r1
left join (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") r2
on st_distance (r1.geom,r2.geom) = 0) as tabla where id2 is null 

union all

select id1 as id,''Points without union'' as errores, point as geom from(select r1.geom as point, r1.id as id1, r2.id as id2
from (select (st_dumppoints(geom)).geom, id from "'|| esch ||'"."'|| nametable ||'") r1 
	  left join "'|| esch ||'"."'|| nametable ||'" r2 
on r1.id<>r2.id and st_expand (r1.geom,1000) && r2.geom and st_distance (r1.geom,r2.geom) = 0)
as tabla where id2 is null

union all

select distinct on (ra.geom)  ra.id, 
  ''Hollow space'' as errores,st_centroid (ra.geom) as geom from 
(select  re.geom, re.contiene, ST_touches (ST_Buffer(st_force2d(st_makevalid(a.geom)),0),re.geom), ST_Buffer(st_force2d(st_makevalid(a.geom)),0),id 
 FROM "'|| esch ||'"."'|| nametable ||'" a, (SELECT  st_contains (((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom),
		ST_ExteriorRing((st_dump(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0))))).geom)) as contiene, 
		((st_dump (ST_Boundary(st_union(ST_Multi(ST_Buffer(st_force2d(geom),0)))))).geom)  AS geom
	   FROM "'|| esch ||'"."'|| nametable ||'") re) ra
 where ra.contiene is null and ra.st_touches = ''true''

union all

select gida as id,''Isolated geometry'' as "error",ST_PointOnSurface(st_makepolygon((st_dump(ST_CollectionExtract(contorno,2))).geom)) from (select t1.id as gida, st_multi (st_boundary(t1.geom)) as contorno, t2.id as gidb
from "'|| esch ||'"."'|| nametable ||'" t1 left join "'|| esch ||'"."'|| nametable ||'" t2
on t1.id<>t2.id and st_intersects (t1.geom,t2.geom)) as tabla where gidb is null

union all

select id,''Repeated vertex'' as "error", ST_PointOnSurface(geomp) as geom 
from (select *, (st_dumppoints (geom)).geom as geomp from "'|| esch ||'"."'|| nametable ||'" 
where not st_asewkb(st_removerepeatedpoints (geom)) = st_asewkb (geom))r
GROUP BY id,geomp
     HAVING COUNT(*)>1
	 
union all

select a.id,concat(''
duplicate geometry'','':'','' id original '',b.id) as "error", ST_PointOnSurface(a.geom) as geom
from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b 
where a.id>b.id and ST_Equals(ST_SnapToGrid(a.geom, 0.005), ST_SnapToGrid(b.geom, 0.005))

union all

select a.id,concat(''overlapping geometries'','':'','' id '',a.id,'' y '',b.id) as "error",ST_PointOnSurface(st_intersection(st_buffer(a.geom,0),st_buffer(b.geom,0))) as geom
from "'|| esch ||'"."'|| nametable ||'" a, "'|| esch ||'"."'|| nametable ||'" b 
where a.geom && b.geom and st_relate (st_buffer(a.geom,0),st_buffer(b.geom,0), ''T********'') and a.id>b.id)re';
END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.seleccion_total_vw(
	esch text,
	nameview text,
	nametable text,
	contenido text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: hace una busqueda por el contenido que se añada en el where */
	EXECUTE 'create view "'|| esch ||'"."'|| nameview ||'"
as select * from "'|| esch ||'"."'|| nametable||'" where  '|| contenido ||'';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.seleccion_total_mv(
	esch text,
	nameview text,
	nametable text,
	contenido text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: hace una busqueda por el contenido que se añada en el where */
	EXECUTE 'create materialized view "'|| esch ||'"."'|| nameview ||'"
as select * from "'|| esch ||'"."'|| nametable||'" where  '|| contenido ||'';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.topotoleranceloop(
	esquema_dest text,
	destino text,
	anedge integer,
	maxtolerance double precision)
    RETURNS double precision
    LANGUAGE 'plpgsql'

    COST 100
    STABLE STRICT 
AS $BODY$DECLARE
  tol float8;
  sql varchar;
BEGIN
  tol := maxtolerance;
  LOOP
        sql := 'select topology.toTopoGeom(geom,''topo_'||destino||''', 1,' || tol || ')
		from "'||esquema_dest||'"."'||destino||'_topo"';
        BEGIN
          RAISE DEBUG 'Running %', sql;
          EXECUTE sql;
          RETURN tol;
        EXCEPTION
         WHEN OTHERS THEN
          RAISE WARNING 'Simplification of edge % with tolerance % failed: %', anedge, tol, SQLERRM;
          tol := round( (tol/1.25) * 1e8 ) / 1e8; -- round to get to zero quicker
          IF tol = 0 THEN RAISE EXCEPTION '%', SQLERRM; END IF;
        END;
  END LOOP;
END
$BODY$;




CREATE OR REPLACE FUNCTION funciones.union_de_atributos_por_centrodies_mv(
	esch text,
	nameview text,
	nametable_geom text,
	tabla_centroides text,
	contenido text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ declare colum character varying;
BEGIN
	/*Descripcion: une los atributos iniciales a las capas generalizadas */
	EXECUTE 'create materialized view  "'|| esch ||'"."'|| nameview ||'" as SELECT 
	'|| contenido ||',a.geom 
 FROM (SELECT '|| contenido ||',
          b.geom,
          st_contains(b.geom, a.centroid) AS st_contains
         FROM "'|| esch ||'"."'|| nametable_geom||'" b,
          "'|| esch ||'"."'||tabla_centroides||'" a ) a WHERE a.st_contains = ''true''';

END; $BODY$;



CREATE OR REPLACE FUNCTION funciones.union_de_atributos_por_centrodies_tbl(
	esch text,
	nameview text,
	nametable_geom text,
	tabla_centroides text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ declare colum character varying;
BEGIN

EXECUTE 'drop table if exists  "'|| esch ||'".centroides;';
EXECUTE 'create table  "'|| esch ||'".centroides as SELECT * FROM  "'|| esch ||'"."'||tabla_centroides||'";';
EXECUTE 'alter table  "'|| esch ||'".centroides drop column geom;';
EXECUTE 'alter table  "'|| esch ||'".centroides rename centroid to geom;';
EXECUTE 'create INDEX ZxasCabW7iWwiBeL8_centroides_geom_gidx
    ON  "'|| esch ||'".centroides USING gist
    (geom);';
	
EXECUTE 'drop table if exists  "'|| esch ||'".cubierta;';
EXECUTE 'create table   "'|| esch ||'".cubierta as select  geom ,layer_ori,row_number ()over() as id_srm from  "'|| esch ||'"."'|| nametable_geom||'"
group by layer_ori, geom;';
EXECUTE 'drop table if exists  "'|| esch ||'".cubierta_centroides;';
EXECUTE 'create table  "'|| esch ||'".cubierta_centroides
as select layer_ori,st_pointonsurface(geom) as geom, id_srm from  "'|| esch ||'".cubierta;';

EXECUTE 'create INDEX ZxasCabW7iWwiBeL8_cubierta_centroides_geom_gidx
    ON  "'|| esch ||'".cubierta_centroides USING gist
    (geom);';

EXECUTE 'drop table if exists  "'|| esch ||'".cubierta_centroides_valores;';
EXECUTE 'create table   "'|| esch ||'".cubierta_centroides_valores as
SELECT DISTINCT ON (h.geom) s.*, h.id_srm,
			h.geom as geom_cubier
		   FROM  "'|| esch ||'".centroides s
			 LEFT JOIN  "'|| esch ||'".cubierta_centroides h 
			 ON st_dwithin(h.geom, s.geom, 200::double precision)
		  ORDER BY h.geom, (st_distance(h.geom, s.geom));';

EXECUTE 'alter table  "'|| esch ||'".cubierta_centroides_valores drop column geom;';
EXECUTE 'alter table  "'|| esch ||'".cubierta_centroides_valores rename geom_cubier to geom;';

EXECUTE 'drop table if exists  "'|| esch ||'"."'|| nameview ||'";';
EXECUTE 'create table  "'|| esch ||'"."'|| nameview ||'" 
as select a.*,b.geom as geom2 from  "'|| esch ||'".cubierta b,
 "'|| esch ||'".cubierta_centroides_valores a where a.id_srm = b.id_srm;';
EXECUTE 'alter table  "'|| esch ||'"."'|| nameview ||'" drop column geom;';
EXECUTE 'alter table  "'|| esch ||'"."'|| nameview ||'" rename geom2 to geom;';
EXECUTE 'alter table  "'|| esch ||'"."'|| nameview ||'" drop column if exists ogc_fid;';
EXECUTE 'alter table  "'|| esch ||'"."'|| nameview ||'" drop column if exists id_srm;';
EXECUTE 'update  "'|| esch ||'"."'|| nameview ||'" set shape_leng = ST_Perimeter(geom);';
EXECUTE 'update  "'|| esch ||'"."'|| nameview ||'" set shape_area = ST_area(geom);';

EXECUTE 'drop table if exists  "'|| esch ||'".centroides;';
EXECUTE 'drop table if exists  "'|| esch ||'".cubierta;';
EXECUTE 'drop table if exists  "'|| esch ||'".cubierta_centroides;';
EXECUTE 'drop table if exists  "'|| esch ||'".cubierta_centroides_valores;';

END; $BODY$;


CREATE OR REPLACE FUNCTION funciones.union_de_atributos_por_centrodies_vw(
	esch text,
	nameview text,
	nametable_geom text,
	tabla_centroides text,
	contenido text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$ declare colum character varying;
BEGIN
	/*Descripcion: une los atributos iniciales a las capas generalizadas */
	EXECUTE 'create view  "'|| esch ||'"."'|| nameview ||'" as SELECT 
	'|| contenido ||',a.geom 
 FROM (SELECT '|| contenido ||',
          b.geom,
          st_contains(b.geom, a.centroid) AS st_contains
         FROM "'|| esch ||'"."'|| nametable_geom||'" b,
          "'|| esch ||'"."'||tabla_centroides||'" a ) a WHERE a.st_contains = ''true''';

END; $BODY$;




CREATE OR REPLACE FUNCTION funciones.union_multiple_mw(
	_schema text,
	_nameview text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
DECLARE
   _safe_target text := quote_ident(_schema) || '.' || quote_ident(_nameview);
BEGIN
 
   EXECUTE (
      SELECT 'CREATE materialized view ' || _safe_target || ' AS TABLE '
          || string_agg(quote_ident(table_schema) || '.'
                     || quote_ident(table_name), ' UNION ALL TABLE ')
      FROM   information_schema.views
      WHERE  table_schema = _schema
      AND    table_name LIKE ('paraunion%')
   );
END
$BODY$;

CREATE OR REPLACE FUNCTION funciones.union_multiple_rail2_mw(
	_schema text,
	_nameview text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
DECLARE
   _safe_target text := quote_ident(_schema) || '.' || quote_ident(_nameview);
BEGIN
 
   EXECUTE (
      SELECT 'CREATE materialized view ' || _safe_target || ' AS TABLE '
          || string_agg(quote_ident(table_schema) || '.'
                     || quote_ident(table_name), ' UNION ALL TABLE ')
      FROM   information_schema.views
      WHERE  table_schema = _schema
      AND    table_name LIKE ('paraunionrail%')
   );
END
$BODY$;


CREATE OR REPLACE FUNCTION funciones.union_multiple_rail2_vw(
	_schema text,
	_nameview text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
DECLARE
   _safe_target text := quote_ident(_schema) || '.' || quote_ident(_nameview);
BEGIN
 
   EXECUTE (
      SELECT 'CREATE view ' || _safe_target || ' AS TABLE '
          || string_agg(quote_ident(table_schema) || '.'
                     || quote_ident(table_name), ' UNION ALL TABLE ')
      FROM   information_schema.views
      WHERE  table_schema = _schema
      AND    table_name LIKE ('paraunionrail%')
   );
END
$BODY$;




CREATE OR REPLACE FUNCTION funciones.union_multiple_vw(
	_schema text,
	_nameview text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
DECLARE
   _safe_target text := quote_ident(_schema) || '.' || quote_ident(_nameview);
BEGIN
 
   EXECUTE (
      SELECT 'CREATE view ' || _safe_target || ' AS TABLE '
          || string_agg(quote_ident(table_schema) || '.'
                     || quote_ident(table_name), ' UNION ALL TABLE ')
      FROM   information_schema.views
      WHERE  table_schema = _schema
      AND    table_name LIKE ('paraunion%')
   );
END
$BODY$;




CREATE OR REPLACE FUNCTION funciones.verficar_datos_mv(
	campo text,
	valor text,
	esch text,
	nameview text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: verifica datos */
	EXECUTE 'CREATE  MATERIALIZED VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT * FROM "'|| esch ||'"."'|| nametable||'"  
	where "'|| campo ||'" = '''|| valor ||'''';

END;
$BODY$;




CREATE OR REPLACE FUNCTION funciones.verficar_datos_vw(
	campo text,
	valor text,
	esch text,
	nameview text,
	nametable text)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$BEGIN
	/*Descripcion: verifica datos */
	EXECUTE 'CREATE  VIEW "'|| esch ||'"."'|| nameview ||'" AS SELECT * FROM "'|| esch ||'"."'|| nametable||'"  
	where "'|| campo ||'" = '''|| valor ||'''';

END;
$BODY$;



CREATE OR REPLACE FUNCTION funciones.zzloop(
	)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
DECLARE 
   r geometry;
BEGIN
FOR r IN 
   SELECT geom
   FROM   "ZxasCabW7iWwiBeL8".edipbpruebaarea
LOOP
   BEGIN -- may be unnecessary
      UPDATE "ZxasCabW7iWwiBeL8".llenar
      SET    geom = st_union (geom,r);
   EXCEPTION when others
      THEN UPDATE "ZxasCabW7iWwiBeL8".llenar
      SET    geom = st_union (geom,r);
   END;
END LOOP;
END
$BODY$;