-----------------------------------------------------------------------
-- BUILD_HISTOGRAM2D(<histogram2d>,<schema>,<tablename>,<columnname>)
-----------------------------------------------------------------------
-- This is a wrapper to the omonimous schema unaware function,
-- thanks to Carl Anderson for the idea.
-----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION build_histogram2d (histogram2d,text,text,text)
RETURNS histogram2d
AS '
BEGIN
	EXECUTE ''SET local search_path = ''||$2||'',public'';
	RETURN public.build_histogram2d($1,$3,$4);
END
' LANGUAGE 'plpgsql'  with (isstrict);


-----------------------------------------------------------------------
-- FIND_EXTENT( <schema name>, <table name>, <column name> )
-----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION find_extent(text,text,text) RETURNS box3d AS
'
DECLARE
	schemaname alias for $1;
	tablename alias for $2;
	columnname alias for $3;
	okay boolean;
 myrec RECORD;

BEGIN
	FOR myrec IN EXECUTE ''SELECT extent("''||columnname||''") FROM "''||schemaname||''"."''||tablename||''"'' LOOP
		return myrec.extent;
	END LOOP; 
END;
'
LANGUAGE 'plpgsql' WITH (isstrict);

-----------------------------------------------------------------------
-- UPDATE_GEOMETRY_STATS()
-----------------------------------------------------------------------
--
-- Only meaningful for PG<80.
-- Gather statisticts about geometry columns for use
-- with cost estimator.
--
-- It is defined also for PG>=80 for back-compatibility
--
-----------------------------------------------------------------------

CREATE OR REPLACE FUNCTION update_geometry_stats()
RETURNS text
AS
'
DECLARE
	result text;
	stated integer;
	fixres text;
BEGIN

	SELECT fix_geometry_columns() INTO fixres;

	UPDATE geometry_columns SET

		stats = (build_histogram2d(create_histogram2d(
			find_extent(f_table_schema, f_table_name, f_geometry_column), 40), 
                        f_table_schema, f_table_name, f_geometry_column))
		FROM pg_class c, pg_attribute a, pg_namespace n
		WHERE n.nspname = f_table_schema::name
		AND c.relname = f_table_name::name
		AND c.relnamespace = n.oid
		AND a.attname = f_geometry_column::name
		AND a.attrelid = c.oid
		AND geometry_columns.attrelid is not null;

	GET DIAGNOSTICS stated = ROW_COUNT;

	result = fixres || '' stats:'' || stated::text;

	return result;
END;
'
LANGUAGE 'plpgsql' ;