<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7652.24">
<TITLE>RE: [postgis-users] Retrieve all tables and views withgeometrycolumns- Drop all tables with geometry columns</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>Small note -- forgive the top posting but this client is limited --<BR>
<BR>
TRUNCATE geometry_columns; is faster and cleaner if you really want to wipe the contents out. No vacuum needed.<BR>
<BR>
G<BR>
<BR>
<BR>
Greg Williamson<BR>
Senior DBA<BR>
GlobeXplorer LLC, a DigitalGlobe company<BR>
<BR>
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.<BR>
<BR>
(My corporate masters made me say this.)<BR>
<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of Obe, Regina<BR>
Sent: Fri 8/3/2007 2:55 PM<BR>
To: PostGIS Users Discussion<BR>
Subject: RE: [postgis-users] Retrieve all tables and views withgeometrycolumns- Drop all tables with geometry columns<BR>
<BR>
Actually if you don't need to cascade, you are better off replacing the DROP TABLE<BR>
with<BR>
strdropsql := 'SELECT DropGeometryTable('''',''' || rec.table_schema || ''',''' || rec.table_name || ''')';<BR>
<BR>
alternatively since you are deleteing all your tables with geometries, just run a<BR>
<BR>
DELETE FROM geometry_columns;<BR>
<BR>
After the drop<BR>
<BR>
TO clean out the geometry_columns meta table.<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net [<A HREF="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] On Behalf Of Obe, Regina<BR>
Sent: Friday, August 03, 2007 4:44 PM<BR>
To: PostGIS Users Discussion<BR>
Subject: RE: [postgis-users] Retrieve all tables and views withgeometrycolumns - Drop all tables with geometry columns<BR>
<BR>
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.<BR>
<BR>
Hope that helps,<BR>
Regina<BR>
<BR>
CREATE OR REPLACE FUNCTION dropgeometrytables()<BR>
RETURNS void AS<BR>
$BODY$<BR>
DECLARE<BR>
strsql text;<BR>
strdropsql text;<BR>
rec RECORD;<BR>
--NOTE: it: the iteration we are currently at<BR>
--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<BR>
BEGIN<BR>
<BR>
strsql := 'SELECT DISTINCT table_schema, table_name<BR>
FROM information_schema.columns<BR>
WHERE udt_name = ''geometry''<BR>
ORDER BY table_schema, table_name';<BR>
FOR rec in EXECUTE (strsql) LOOP<BR>
strdropsql := 'DROP TABLE ' || rec.table_schema || '.' || rec.table_name || ' CASCADE';<BR>
EXECUTE strdropsql;<BR>
END LOOP;<BR>
END<BR>
$BODY$<BR>
LANGUAGE 'plpgsql' VOLATILE<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net [<A HREF="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] On Behalf Of Thorsten Kraus<BR>
Sent: Friday, August 03, 2007 4:23 AM<BR>
To: PostGIS Users Discussion<BR>
Subject: AW: [postgis-users] Retrieve all tables and views with geometrycolumns<BR>
<BR>
Hello,<BR>
<BR>
I would like to drop all tables from my database which contain geometry columns. Does someone have a solution for this?<BR>
<BR>
Regards,<BR>
Thorsten<BR>
<BR>
-----Ursprüngliche Nachricht-----<BR>
Von: postgis-users-bounces@postgis.refractions.net<BR>
[<A HREF="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>]Im Auftrag von<BR>
Michael Fuhr<BR>
Gesendet: Mittwoch, 1. August 2007 13:00<BR>
An: PostGIS Users Discussion<BR>
Betreff: Re: [postgis-users] Retrieve all tables and views with<BR>
geometrycolumns<BR>
<BR>
<BR>
On Wed, Aug 01, 2007 at 11:42:15AM +0200, Boehm, Andreas wrote:<BR>
> I would like to list all the geometry columns in a database. So the user<BR>
> can select the features he or she wants to see. Therefore I need to<BR>
> retrieve the database's metadata.<BR>
> With "select * from geometry_columns" I'm able to get a list of all<BR>
> tables with geometry columns. But I don't get information about the<BR>
> _views_ with a geometry column.<BR>
> Do I have to parse the definition text in pg_views? Maybe there is an<BR>
> easier way...<BR>
<BR>
You could query pg_catalog.pg_attribute or information_schema.columns.<BR>
<BR>
<A HREF="http://www.postgresql.org/docs/8.2/interactive/catalog-pg-attribute.html">http://www.postgresql.org/docs/8.2/interactive/catalog-pg-attribute.html</A><BR>
<A HREF="http://www.postgresql.org/docs/8.2/interactive/infoschema-columns.html">http://www.postgresql.org/docs/8.2/interactive/infoschema-columns.html</A><BR>
<BR>
SELECT n.nspname, c.relname, a.attname<BR>
FROM pg_attribute AS a<BR>
JOIN pg_class AS c ON c.oid = a.attrelid<BR>
JOIN pg_namespace AS n ON n.oid = c.relnamespace<BR>
WHERE a.atttypid = 'geometry'::regtype<BR>
AND NOT a.attisdropped<BR>
AND c.relkind IN ('r', 'v')<BR>
ORDER BY n.nspname, c.relname, a.attname;<BR>
<BR>
or<BR>
<BR>
SELECT table_schema, table_name, column_name<BR>
FROM information_schema.columns<BR>
WHERE udt_name = 'geometry'<BR>
ORDER BY table_schema, table_name;<BR>
<BR>
--<BR>
Michael Fuhr<BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
-----------------------------------------<BR>
The substance of this message, including any attachments, may be<BR>
confidential, legally privileged and/or exempt from disclosure<BR>
pursuant to Massachusetts law. It is intended<BR>
solely for the addressee. If you received this in error, please<BR>
contact the sender and delete the material from any computer.<BR>
<BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>