Forums PostgreSQL.fr

-- 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
'
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;
	schema_ok bool;
	real_schema name;
 
BEGIN
 
	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'')) )
	THEN
		RAISE EXCEPTION ''Invalid type name - valid ones are: 
			GEOMETRY, GEOMETRYCOLLECTION, POINT, 
			MULTIPOINT, POLYGON, MULTIPOLYGON, 
			LINESTRING, MULTILINESTRING,
			GEOMETRYCOLLECTIONM, POINTM, 
			MULTIPOINTM, POLYGONM, MULTIPOLYGONM, 
			LINESTRINGM, or MULTILINESTRINGM '';
		return ''fail'';
	END IF;
 
	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;
 
	IF ( schema_name != '''' ) THEN
		schema_ok = ''f'';
		FOR rec IN SELECT nspname FROM pg_namespace WHERE text(nspname) = schema_name LOOP
			schema_ok := ''t'';
		END LOOP;
 
		if ( schema_ok <> ''t'' ) THEN
			RAISE NOTICE ''Invalid schema name - using current_schema()'';
			SELECT current_schema() into real_schema;
		ELSE
			real_schema = schema_name;
		END IF;
 
	ELSE
		SELECT current_schema() into real_schema;
	END IF;
 
 
	-- Add geometry column
 
	EXECUTE ''ALTER TABLE '' ||
		quote_ident(real_schema) || ''.'' || quote_ident(table_name)
		|| '' ADD COLUMN '' || quote_ident(column_name) || 
		'' geometry '';
 
 
	-- Delete stale record in geometry_column (if any)
 
	EXECUTE ''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);
 
 
	-- Add record in geometry_column 
 
	EXECUTE ''INSERT INTO geometry_columns VALUES ('' ||
		quote_literal('''') || '','' ||
		quote_literal(real_schema) || '','' ||
		quote_literal(table_name) || '','' ||
		quote_literal(column_name) || '','' ||
		new_dim || '','' || new_srid || '','' ||
		quote_literal(new_type) || '')'';
 
	-- Add table checks
 
	EXECUTE ''ALTER TABLE '' || 
		quote_ident(real_schema) || ''.'' || quote_ident(table_name)
		|| '' ADD CONSTRAINT '' 
		|| quote_ident(''enforce_srid_'' || column_name)
		|| '' CHECK (SRID('' || quote_ident(column_name) ||
		'') = '' || new_srid || '')'' ;
 
	EXECUTE ''ALTER TABLE '' || 
		quote_ident(real_schema) || ''.'' || quote_ident(table_name)
		|| '' ADD CONSTRAINT ''
		|| quote_ident(''enforce_dims_'' || column_name)
		|| '' CHECK (ndims('' || quote_ident(column_name) ||
		'') = '' || new_dim || '')'' ;
 
	IF (not(new_type = ''GEOMETRY'')) THEN
		EXECUTE ''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)'';
	END IF;
 
	return 
		real_schema || ''.'' || 
		table_name || ''.'' || column_name ||
		'' SRID:'' || new_srid ||
		'' TYPE:'' || new_type || 
		'' DIMS:'' || new_dim || chr(10) || '' ''; 
END;
'
  LANGUAGE 'plpgsql' VOLATILE STRICT;