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

[Gregory Williamson]

Small note -- forgive the top posting but this client is limited -- 

TRUNCATE geometry_columns; is faster and cleaner if you really want to wipe the contents out. No vacuum needed.

G


Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)



-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net on behalf of Obe, Regina
Sent: Fri 8/3/2007 2:55 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Retrieve all tables and views withgeometrycolumns- Drop all tables with geometry columns
 
Actually if you don't need to cascade, you are better off replacing the DROP TABLE
with 
strdropsql := 'SELECT DropGeometryTable('''',''' || rec.table_schema || ''',''' || rec.table_name || ''')';

alternatively since you are deleteing all your tables with geometries, just run a 

DELETE FROM geometry_columns;

After the drop

TO clean out the geometry_columns meta table.

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Obe, Regina
Sent: Friday, August 03, 2007 4:44 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Retrieve all tables and views withgeometrycolumns - Drop all tables with geometry columns

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.

_______________________________________________
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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070803/013c0290/attachment.html>