PostGIS - Function to create missing spatial indexes in your whole database

PostGIS_create_missing_spatial_indexes.sql

-- Allow to list all layers with no spatial index
-- To get the list
-- SELECT * FROM create_missing_spatial_indexes(True);
-- To create automatically the missing spatial indexes
-- SELECT * FROM create_missing_spatial_indexes(False);

DROP FUNCTION IF EXISTS create_missing_spatial_indexes();
DROP FUNCTION IF EXISTS create_missing_spatial_indexes(  BOOLEAN );
CREATE OR REPLACE FUNCTION create_missing_spatial_indexes(simulate BOOLEAN DEFAULT FALSE)
RETURNS TABLE (
    schemaname text, tablename text, fieldname text
) AS
$BODY$
DECLARE sql_text TEXT;
DECLARE sql_template TEXT;
DECLARE sch TEXT;
DECLARE tab TEXT;
DECLARE att TEXT;
DECLARE rec RECORD;
BEGIN

    FOR rec IN
        SELECT nspname, relname, attname
        FROM
        (
            SELECT
            n.nspname,
            c.relname,
            c.oid AS relid,
            a.attname,
            a.attnum
            FROM pg_attribute a
            INNER JOIN pg_class c ON (a.attrelid=c.oid)
            INNER JOIN pg_type t ON (a.atttypid=t.oid)
            INNER JOIN pg_namespace n ON (c.relnamespace=n.oid)
            WHERE t.typname='geometry'
            AND   c.relkind='r'
        ) g
        LEFT JOIN pg_index i ON (g.relid = i.indrelid AND g.attnum = ANY(i.indkey))
        WHERE i IS NULL
    LOOP
        schemaname := rec.nspname;
        tablename := rec.relname;
        fieldname := rec.attname;

        IF NOT simulate THEN
            sql_template := 'CREATE INDEX ON "%s"."%s" USING GIST ("%s")';
            sql_text := format(sql_template, schemaname, tablename, fieldname);
            RAISE NOTICE '%', sql_text;
            EXECUTE sql_text;
        END IF;

        RETURN NEXT;

    END LOOP;

END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Yuo can spiff it up a little bit...

CREATE OR REPLACE FUNCTION create_missing_spatial_indexes(simulate BOOLEAN DEFAULT FALSE) 
	RETURNS SETOF TEXT AS
$BODY$
DECLARE r RECORD;
BEGIN
	FOR r IN 
		SELECT format('CREATE INDEX ON "%s"."%s" USING GIST ("%s")', n.nspname, c.relname, a.attname) AS cmd
		FROM pg_attribute a
			INNER JOIN pg_class     c ON (a.attrelid=c.oid)
			INNER JOIN pg_type      t ON (a.atttypid=t.oid)
			INNER JOIN pg_namespace n ON (c.relnamespace=n.oid)
			LEFT JOIN  pg_index     i ON (c.oid = i.indrelid AND a.attnum = ANY(i.indkey))
		WHERE t.typname='geometry' AND c.relkind='r' AND i.indkey is null

	LOOP
		IF NOT simulate THEN
			EXECUTE r.cmd;
		END IF;
		RETURN NEXT r.cmd; 
	END LOOP;
	RETURN;
 END
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;

Regards
Bo Victor

Thanks @bvthomsen I will try it. I personally used the table output to return the schema, table and geom column in a more friendly way, instead of the SQL command run for each index.