[postgis-users] Retrieve all tables and views with geometrycolumns - Drop all tables with geometry columns

[Obe, Regina]

Seems kind of dangerous.  You sure you want to do that?  You can write a plpgsql function to do it something like below.  Note I put in a CASCADE clause which will delete all objects that depend on the table as well.  If you leave that part out and a table is used in a view (and I think functions, not absolutely sure about that) then the DROP will fail.

Hope that helps,
Regina

CREATE OR REPLACE FUNCTION dropgeometrytables()
  RETURNS void AS
$BODY$
DECLARE
    strsql text;
    strdropsql text;
    rec RECORD;
--NOTE: it: the iteration we are currently at 
--start at the bounding box of the object (expand 0) and move up until it has collected more objects than we need or it = maxslices whichever event happens first
BEGIN

     strsql := 'SELECT DISTINCT table_schema, table_name
  FROM information_schema.columns
 WHERE udt_name = ''geometry''
 ORDER BY table_schema, table_name';
        FOR rec in EXECUTE (strsql) LOOP
		strdropsql := 'DROP TABLE ' || rec.table_schema || '.' || rec.table_name || ' CASCADE';
            EXECUTE strdropsql;
        END LOOP;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Thorsten Kraus
Sent: Friday, August 03, 2007 4:23 AM
To: PostGIS Users Discussion
Subject: AW: [postgis-users] Retrieve all tables and views with geometrycolumns

Hello,

I would like to drop all tables from my database which contain geometry columns. Does someone have a solution for this?

Regards,
Thorsten

-----Ursprüngliche Nachricht-----
Von: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net]Im Auftrag von
Michael Fuhr
Gesendet: Mittwoch, 1. August 2007 13:00
An: PostGIS Users Discussion
Betreff: Re: [postgis-users] Retrieve all tables and views with
geometrycolumns


On Wed, Aug 01, 2007 at 11:42:15AM +0200, Boehm, Andreas wrote:
> I would like to list all the geometry columns in a database. So the user
> can select the features he or she wants to see. Therefore I need to
> retrieve the database's metadata. 
> With "select * from geometry_columns" I'm able to get a list of all
> tables with geometry columns. But I don't get information about the
> _views_ with a geometry column. 
> Do I have to parse the definition text in pg_views? Maybe there is an
> easier way...

You could query pg_catalog.pg_attribute or information_schema.columns.

http://www.postgresql.org/docs/8.2/interactive/catalog-pg-attribute.html
http://www.postgresql.org/docs/8.2/interactive/infoschema-columns.html

SELECT n.nspname, c.relname, a.attname
  FROM pg_attribute AS a
  JOIN pg_class AS c ON c.oid = a.attrelid
  JOIN pg_namespace AS n ON n.oid = c.relnamespace
 WHERE a.atttypid = 'geometry'::regtype
   AND NOT a.attisdropped
   AND c.relkind IN ('r', 'v')
 ORDER BY n.nspname, c.relname, a.attname;

or

SELECT table_schema, table_name, column_name
  FROM information_schema.columns
 WHERE udt_name = 'geometry'
 ORDER BY table_schema, table_name;

-- 
Michael Fuhr
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.