PostGre函数

-- Function: dropgeometrycolumn("varchar", "varchar", "varchar", "varchar")

-- DROP FUNCTION dropgeometrycolumn("varchar", "varchar", "varchar", "varchar");

CREATE OR REPLACE FUNCTION dropgeometrycolumn("varchar", "varchar", "varchar", "varchar")
  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;
    myrec RECORD;
    okay boolean;
    real_schema name;

BEGIN



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

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

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




     -- Find out if the column is in the geometry_columns table
    okay = 'f';
    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 := 't';
    END LOOP;
    IF (okay <> 't') THEN
        RAISE EXCEPTION 'column not found in geometry_columns table';
        RETURN 'f';
    END IF;

    -- Remove ref from geometry_columns table
    EXECUTE 'delete from geometry_columns where f_table_schema = ' ||
        quote_literal(real_schema) || ' and f_table_name = ' ||
        quote_literal(table_name)  || ' and f_geometry_column = ' ||
        quote_literal(column_name);
   
    -- 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;
$BODY$
  LANGUAGE 'plpgsql ' VOLATILE STRICT;
ALTER FUNCTION dropgeometrycolumn("varchar", "varchar", "varchar", "varchar") OWNER TO postgres;