PostGIS dropgeometrycolumn function (Was: Re: [7.4] "permissions problem" with pl/pgsql function )

On Mon, 19 Jan 2004, Tom Lane wrote:

> "Marc G. Fournier" <scrappy@hub.org> writes:
> > Just had a client point this out to me, and am wondering if its supposed
> > to happen:
>
> > 420_test=> select
> > dropgeometrycolumn('420_test','lroadline61','roads61_geom');
> > ERROR:  permission denied for relation pg_attribute
> > CONTEXT:  PL/pgSQL function "dropgeometrycolumn" line 19 at execute statement
>
> Can't tell much about this without seeing the contents of the function ...
> in particular, what SQL command is it trying to execute when it chokes?

The function is executing:
EXECUTE ''update pg_attribute set attnotnull = false from pg_class where                 pg_attribute.attrelid =
pg_class.oidand pg_class.relname = '' ||                 quote_literal(table_name) ||'' and pg_attribute.attname = ''
||                quote_literal(column_name);
 


I'm going to hit up the PostGis folks, since right at the top of the
function it stats:

-- There is no ALTER TABLE DROP COLUMN command in postgresql
-- There is no ALTER TABLE DROP CONSTRAINT command in postgresql
-- So, we:
--   1. remove the unwanted geom column reference from the
--      geometry_columns table
--   2. update the table so that the geometry column is all NULLS
--      This is okay since the CHECK srid(geometry) = <srid> is not
--      checked if geometry is NULL (the isstrict attribute on srid())
--   3. add another constraint that the geometry column must be NULL
-- This, effectively kills the geometry column
--   (a) its not in the geometry_column table
--   (b) it only has nulls in it
--   (c) you cannot add anything to the geom column because it must be NULL
-- 
-- This will screw up if you put a NOT NULL constraint on the geometry
-- column, so the first thing we must do is remove this constraint (its a
-- modification of the pg_attribute system table)
--
-- We also check to see if the table/column exists in the geometry_columns
-- table

Anyone on this list working with the PostGis development team?

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664