-- Function: addgeometrycolumn(character varying, character varying, character varying, character varying, integer, character varying, integer)

-- DROP FUNCTION addgeometrycolumn(character varying, character varying, character varying, character varying, integer, character varying, integer);

CREATE OR REPLACE FUNCTION addgeometrycolumn(CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, INTEGER, CHARACTER VARYING, INTEGER)
  RETURNS text AS
$BODY$
DECLARE
    catalog_name alias FOR $1;
    schema_name alias FOR $2;
    TABLE_NAME alias FOR $3;
    column_name alias FOR $4;
    new_srid alias FOR $5;
    new_type alias FOR $6;
    new_dim alias FOR $7;
    rec RECORD;
    sr VARCHAR;
    real_schema name;
    SQL text;

BEGIN

    -- Verify geometry type
    IF ( NOT ( (new_type = 'GEOMETRY') OR
               (new_type = 'GEOMETRYCOLLECTION') OR
               (new_type = 'POINT') OR
               (new_type = 'MULTIPOINT') OR
               (new_type = 'POLYGON') OR
               (new_type = 'MULTIPOLYGON') OR
               (new_type = 'LINESTRING') OR
               (new_type = 'MULTILINESTRING') OR
               (new_type = 'GEOMETRYCOLLECTIONM') OR
               (new_type = 'POINTM') OR
               (new_type = 'MULTIPOINTM') OR
               (new_type = 'POLYGONM') OR
               (new_type = 'MULTIPOLYGONM') OR
               (new_type = 'LINESTRINGM') OR
               (new_type = 'MULTILINESTRINGM') OR
               (new_type = 'CIRCULARSTRING') OR
               (new_type = 'CIRCULARSTRINGM') OR
               (new_type = 'COMPOUNDCURVE') OR
               (new_type = 'COMPOUNDCURVEM') OR
               (new_type = 'CURVEPOLYGON') OR
               (new_type = 'CURVEPOLYGONM') OR
               (new_type = 'MULTICURVE') OR
               (new_type = 'MULTICURVEM') OR
               (new_type = 'MULTISURFACE') OR
               (new_type = 'MULTISURFACEM')) )
    THEN
        RAISE EXCEPTION 'Invalid type name - valid ones are:
    POINT, MULTIPOINT,
    LINESTRING, MULTILINESTRING,
    POLYGON, MULTIPOLYGON,
    CIRCULARSTRING, COMPOUNDCURVE, MULTICURVE,
    CURVEPOLYGON, MULTISURFACE,
    GEOMETRY, GEOMETRYCOLLECTION,
    POINTM, MULTIPOINTM,
    LINESTRINGM, MULTILINESTRINGM,
    POLYGONM, MULTIPOLYGONM,
    CIRCULARSTRINGM, COMPOUNDCURVEM, MULTICURVEM
    CURVEPOLYGONM, MULTISURFACEM,
    or GEOMETRYCOLLECTIONM';
        RETURN 'fail';
    END IF;


    -- Verify dimension
    IF ( (new_dim >4) OR (new_dim <0) ) THEN
        RAISE EXCEPTION 'invalid dimension';
        RETURN 'fail';
    END IF;

    IF ( (new_type LIKE '%M') AND (new_dim!=3) ) THEN
        RAISE EXCEPTION 'TypeM needs 3 dimensions';
        RETURN 'fail';
    END IF;


    -- Verify SRID
    IF ( new_srid != -1 ) THEN
        SELECT SRID INTO sr FROM spatial_ref_sys WHERE SRID = new_srid;
        IF NOT FOUND THEN
            RAISE EXCEPTION 'AddGeometryColumns() - invalid SRID';
            RETURN 'fail';
        END IF;
    END IF;


    -- Verify schema
    IF ( schema_name IS NOT NULL AND schema_name != '' ) THEN
        SQL := 'SELECT nspname FROM pg_namespace ' ||
            'WHERE text(nspname) = ' || quote_literal(schema_name) ||
            'LIMIT 1';
        RAISE DEBUG '%', SQL;
        EXECUTE SQL INTO real_schema;

        IF ( real_schema IS NULL ) THEN
            RAISE EXCEPTION 'Schema % is not a valid schemaname', quote_literal(schema_name);
            RETURN 'fail';
        END IF;
    END IF;

    IF ( real_schema IS NULL ) THEN
        RAISE DEBUG 'Detecting schema';
        SQL := 'SELECT n.nspname AS schemaname ' ||
            'FROM pg_catalog.pg_class c ' ||
              'JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace ' ||
            'WHERE c.relkind = ' || quote_literal('r') ||
            ' AND n.nspname NOT IN (' || quote_literal('pg_catalog') || ', ' || quote_literal('pg_toast') || ')' ||
            ' AND pg_catalog.pg_table_is_visible(c.oid)' ||
            ' AND c.relname = ' || quote_literal(TABLE_NAME);
        RAISE DEBUG '%', SQL;
        EXECUTE SQL INTO real_schema;

        IF ( real_schema IS NULL ) THEN
            RAISE EXCEPTION 'Table % does not occur in the search_path', quote_literal(TABLE_NAME);
            RETURN 'fail';
        END IF;
    END IF;


    -- Add geometry column to table
    SQL := 'ALTER TABLE ' ||
        quote_ident(real_schema) || '.' || quote_ident(TABLE_NAME)
        || ' ADD COLUMN ' || quote_ident(column_name) ||
        ' geometry ';
    RAISE DEBUG '%', SQL;
    EXECUTE SQL;


    -- Delete stale record in geometry_columns (if any)
    SQL := 'DELETE FROM geometry_columns WHERE
        f_table_catalog = ' || quote_literal('') ||
        ' AND f_table_schema = ' ||
        quote_literal(real_schema) ||
        ' AND f_table_name = ' || quote_literal(TABLE_NAME) ||
        ' AND f_geometry_column = ' || quote_literal(column_name);
    RAISE DEBUG '%', SQL;
    EXECUTE SQL;


    -- Add record in geometry_columns
    SQL := 'INSERT INTO geometry_columns (f_table_catalog,f_table_schema,f_table_name,' ||
                                          'f_geometry_column,coord_dimension,srid,type)' ||
        ' VALUES (' ||
        quote_literal('') || ',' ||
        quote_literal(real_schema) || ',' ||
        quote_literal(TABLE_NAME) || ',' ||
        quote_literal(column_name) || ',' ||
        new_dim::text || ',' ||
        new_srid::text || ',' ||
        quote_literal(new_type) || ')';
    RAISE DEBUG '%', SQL;
    EXECUTE SQL;


    -- Add table CHECKs
    SQL := 'ALTER TABLE ' ||
        quote_ident(real_schema) || '.' || quote_ident(TABLE_NAME)
        || ' ADD CONSTRAINT '
        || quote_ident('enforce_srid_' || column_name)
        || ' CHECK (ST_SRID(' || quote_ident(column_name) ||
        ') = ' || new_srid::text || ')' ;
    RAISE DEBUG '%', SQL;
    EXECUTE SQL;

    SQL := 'ALTER TABLE ' ||
        quote_ident(real_schema) || '.' || quote_ident(TABLE_NAME)
        || ' ADD CONSTRAINT '
        || quote_ident('enforce_dims_' || column_name)
        || ' CHECK (ST_NDims(' || quote_ident(column_name) ||
        ') = ' || new_dim::text || ')' ;
    RAISE DEBUG '%', SQL;
    EXECUTE SQL;

    IF ( NOT (new_type = 'GEOMETRY')) THEN
        SQL := 'ALTER TABLE ' ||
            quote_ident(real_schema) || '.' || quote_ident(TABLE_NAME) || ' ADD CONSTRAINT ' ||
            quote_ident('enforce_geotype_' || column_name) ||
            ' CHECK (GeometryType(' ||
            quote_ident(column_name) || ')=' ||
            quote_literal(new_type) || ' OR (' ||
            quote_ident(column_name) || ') is null)';
        RAISE DEBUG '%', SQL;
        EXECUTE SQL;
    END IF;

    RETURN
        real_schema || '.' ||
        TABLE_NAME || '.' || column_name ||
        ' SRID:' || new_srid::text ||
        ' TYPE:' || new_type ||
        ' DIMS:' || new_dim::text || ' ';
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE STRICT
  COST 100;
ALTER FUNCTION addgeometrycolumn(CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, INTEGER, CHARACTER VARYING, INTEGER) OWNER TO "raschia-g";
COMMENT ON FUNCTION addgeometrycolumn(CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, INTEGER, CHARACTER VARYING, INTEGER) IS 'args: catalog_name, schema_name, table_name, column_name, srid, type, dimension - Adds a geometry column to an existing table of attributes.';