-- Function: public.populate_geometry_columns(oid)

-- DROP FUNCTION public.populate_geometry_columns(oid);

CREATE OR REPLACE FUNCTION public.populate_geometry_columns(tbl_oid oid)
  RETURNS text AS
$BODY$
DECLARE
    gcs         RECORD;
    gc          RECORD;
    gsrid       integer;
    gndims      integer;
    gtype       text;
    query       text;
    gc_is_valid boolean;
    
BEGIN
    FOR gcs IN 
        SELECT n.nspname, c.relname, a.attname
            FROM pg_class c, 
                 pg_attribute a, 
                 pg_type t, 
                 pg_namespace n
            WHERE c.relkind = 'r'
            AND t.typname = 'geometry'
            AND a.attisdropped = false
            AND a.atttypid = t.oid
            AND a.attrelid = c.oid
            AND c.relnamespace = n.oid
	    AND n.nspname NOT ILIKE 'pg_temp%'
            AND c.oid = tbl_oid
        LOOP
        
        RAISE DEBUG 'Processing %.%.%', gcs.nspname, gcs.relname, gcs.attname;

        DELETE FROM geometry_columns 
          WHERE f_table_schema = quote_ident(gcs.nspname) 
          AND f_table_name = quote_ident(gcs.relname)
          AND f_geometry_column = quote_ident(gcs.attname);
        
        gc_is_valid := true;
        
        -- Try to find srid check from system tables (pg_constraint)
        gsrid := 
            (SELECT replace(split_part(s.consrc, ' = ', 2), ')', '') 
             FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s 
             WHERE n.nspname = gcs.nspname 
             AND c.relname = gcs.relname 
             AND a.attname = gcs.attname 
             AND a.attrelid = c.oid
             AND s.connamespace = n.oid
             AND s.conrelid = c.oid
             AND a.attnum = ANY (s.conkey)
             AND s.consrc LIKE '%srid(% = %');
        IF (gsrid IS NULL) THEN 
            -- Try to find srid from the geometry itself
            EXECUTE 'SELECT public.srid(' || quote_ident(gcs.attname) || ') 
                     FROM ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || ' 
                     WHERE ' || quote_ident(gcs.attname) || ' IS NOT NULL LIMIT 1' 
                INTO gc;
            gsrid := gc.srid;
            
            -- Try to apply srid check to column
            IF (gsrid IS NOT NULL) THEN
                BEGIN
                    EXECUTE 'ALTER TABLE ONLY ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || ' 
                             ADD CONSTRAINT ' || quote_ident('enforce_srid_' || gcs.attname) || ' 
                             CHECK (srid(' || quote_ident(gcs.attname) || ') = ' || gsrid || ')';
                EXCEPTION
                    WHEN check_violation THEN
                        RAISE WARNING 'Not inserting \'%\' in \'%.%\' into geometry_columns: could not apply constraint CHECK (srid(%) = %)', quote_ident(gcs.attname), quote_ident(gcs.nspname), quote_ident(gcs.relname), quote_ident(gcs.attname), gsrid;
                        gc_is_valid := false;
                END;
            END IF;
        END IF;
        
        -- Try to find ndims check from system tables (pg_constraint)
        gndims := 
            (SELECT replace(split_part(s.consrc, ' = ', 2), ')', '') 
             FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s 
             WHERE n.nspname = gcs.nspname 
             AND c.relname = gcs.relname 
             AND a.attname = gcs.attname 
             AND a.attrelid = c.oid
             AND s.connamespace = n.oid
             AND s.conrelid = c.oid
             AND a.attnum = ANY (s.conkey)
             AND s.consrc LIKE '%ndims(% = %');
        IF (gndims IS NULL) THEN
            -- Try to find ndims from the geometry itself
            EXECUTE 'SELECT public.ndims(' || quote_ident(gcs.attname) || ') 
                     FROM ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || ' 
                     WHERE ' || quote_ident(gcs.attname) || ' IS NOT NULL LIMIT 1' 
                INTO gc;
            gndims := gc.ndims;
            
            -- Try to apply ndims check to column
            IF (gndims IS NOT NULL) THEN
                BEGIN
                    EXECUTE 'ALTER TABLE ONLY ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || ' 
                             ADD CONSTRAINT ' || quote_ident('enforce_dims_' || gcs.attname) || ' 
                             CHECK (ndims(' || quote_ident(gcs.attname) || ') = '||gndims||')';
                EXCEPTION
                    WHEN check_violation THEN
                        RAISE WARNING 'Not inserting \'%\' in \'%.%\' into geometry_columns: could not apply constraint CHECK (ndims(%) = %)', quote_ident(gcs.attname), quote_ident(gcs.nspname), quote_ident(gcs.relname), quote_ident(gcs.attname), gndims;
                        gc_is_valid := false;
                END;
            END IF;
        END IF;
        
        -- Try to find geotype check from system tables (pg_constraint)
        gtype := 
            (SELECT replace(split_part(s.consrc, '''', 2), ')', '') 
             FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s 
             WHERE n.nspname = gcs.nspname 
             AND c.relname = gcs.relname 
             AND a.attname = gcs.attname 
             AND a.attrelid = c.oid
             AND s.connamespace = n.oid
             AND s.conrelid = c.oid
             AND a.attnum = ANY (s.conkey)
             AND s.consrc LIKE '%geometrytype(% = %');
        IF (gtype IS NULL) THEN
            -- Try to find geotype from the geometry itself
            EXECUTE 'SELECT public.geometrytype(' || quote_ident(gcs.attname) || ') 
                     FROM ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || ' 
                     WHERE ' || quote_ident(gcs.attname) || ' IS NOT NULL LIMIT 1' 
                INTO gc;
            gtype := gc.geometrytype;
            IF (gtype IS NULL) THEN
                gtype := 'GEOMETRY';
            END IF;
            
            -- Try to apply geometrytype check to column
            BEGIN
                EXECUTE 'ALTER TABLE ONLY ' || quote_ident(gcs.nspname) || '.' || quote_ident(gcs.relname) || ' 
                ADD CONSTRAINT ' || quote_ident('enforce_geotype_' || gcs.attname) || ' 
                CHECK ((geometrytype(' || quote_ident(gcs.attname) || ') = ' || quote_literal(gtype) || ') OR (' || quote_ident(gcs.attname) || ' IS NULL))';
            EXCEPTION
                WHEN check_violation THEN
                    -- No geometry check can be applied. This column contains a number of geometry types.
                    RAISE WARNING 'Could not add geometry type check (%) to table column: %.%.%', gtype, quote_ident(gcs.nspname),quote_ident(gcs.relname),quote_ident(gcs.attname);
            END;
        END IF;
                
        IF (gsrid IS NULL) THEN             
            RAISE WARNING 'Not inserting \'%\' in \'%.%\' into geometry_columns: could not determine the srid', quote_ident(gcs.attname), quote_ident(gcs.nspname), quote_ident(gcs.relname);
        ELSIF (gndims IS NULL) THEN
            RAISE WARNING 'Not inserting \'%\' in \'%.%\' into geometry_columns: could not determine the number of dimensions', quote_ident(gcs.attname), quote_ident(gcs.nspname), quote_ident(gcs.relname);
        ELSE
            -- Only insert into geometry_columns if table constraints could be applied.
            IF (gc_is_valid) THEN
                INSERT INTO geometry_columns (f_table_catalog,f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, type) 
                VALUES ('', gcs.nspname, gcs.relname, gcs.attname, gndims, gsrid, gtype);
            END IF;
        END IF;
    END LOOP;

    RETURN 'done';
END

$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
