pggeodb.nancy.inra.fr/db_cefs - db_cefs on pggeodb.nancy.inra.fr

Function: dropgeometrycolumn

Schema

public

Owner

postgres

Descriptions

args: catalog_name, schema_name, table_name, column_name - Removes a geometry column from a spatial table.

Options

Definition

CREATE OR REPLACE FUNCTION public.dropgeometrycolumn (
 catalog_name varchar,
 schema_name varchar,
 table_name varchar,
 column_name varchar
)
RETURNS text AS
$span$
DECLARE
myrec RECORD;
okay boolean;
real_schema name;

BEGIN


-- Find, check or fix schema_name
IF ( schema_name != '' ) THEN
okay = false;

FOR myrec IN SELECT nspname FROM pg_namespace WHERE text(nspname) = schema_name LOOP
okay := true;
END LOOP;

IF ( okay <>  true ) 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;

-- Find out if the column is in the geometry_columns table
okay = false;
FOR myrec IN SELECT * from geometry_columns where f_table_schema = text(real_schema) and f_table_name = table_name and f_geometry_column = column_name LOOP
okay := true;
END LOOP;
IF (okay <> true) THEN
RAISE EXCEPTION 'column not found in geometry_columns table';
RETURN false;
END IF;

-- Remove table column
EXECUTE 'ALTER TABLE ' || quote_ident(real_schema) || '.' ||
quote_ident(table_name) || ' DROP COLUMN ' ||
quote_ident(column_name);

RETURN real_schema || '.' || table_name || '.' || column_name ||' effectively removed.';

END;
$span$
LANGUAGE 'plpgsql'
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER
COST 100;

COMMENT ON FUNCTION public.dropgeometrycolumn(catalog_name varchar, schema_name varchar, table_name varchar, column_name varchar)
IS 'args: catalog_name, schema_name, table_name, column_name - Removes a geometry column from a spatial table.';

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 13/03/2014 13:23