bbBDV9XY

1DROP FUNCTION IF EXISTS create_missing_spatial_indexes();
2DROP FUNCTION IF EXISTS create_missing_spatial_indexes(  BOOLEAN );
3CREATE OR REPLACE FUNCTION create_missing_spatial_indexes(simulate BOOLEAN DEFAULT FALSE)
4RETURNS TABLE (
5    schemaname text, tablename text, fieldname text
6) AS
7$BODY$
8DECLARE sql_text TEXT;
9DECLARE sql_template TEXT;
10DECLARE sch TEXT;
11DECLARE tab TEXT;
12DECLARE att TEXT;
13DECLARE rec RECORD;
14BEGIN
15
16    FOR rec IN
17        SELECT nspname, relname, attname
18        FROM
19        (
20            SELECT
21            n.nspname,
22            c.relname,
23            c.oid AS relid,
24            a.attname,
25            a.attnum
26            FROM pg_attribute a
27            INNER JOIN pg_class c ON (a.attrelid=c.oid)
28            INNER JOIN pg_type t ON (a.atttypid=t.oid)
29            INNER JOIN pg_namespace n ON (c.relnamespace=n.oid)
30            WHERE t.typname='geometry'
31            AND   c.relkind='r'
32        ) g
33        LEFT JOIN pg_index i ON (g.relid = i.indrelid AND g.attnum = ANY(i.indkey))
34        WHERE i IS NULL
35    LOOP
36        schemaname := rec.nspname;
37        tablename := rec.relname;
38        fieldname := rec.attname;
39
40        IF NOT simulate THEN
41            sql_template := 'CREATE INDEX ON "%s"."%s" USING GIST ("%s")';
42            sql_text := format(sql_template, schemaname, tablename, fieldname);
43            RAISE NOTICE '%', sql_text;
44            EXECUTE sql_text;
45        END IF;
46
47        RETURN NEXT;
48
49    END LOOP;
50
51END
52$BODY$
53LANGUAGE plpgsql VOLATILE
54COST 100;