cartoninjas.net

Recently I had to automate postgis export via ogr2ogr. In order to list all the sptatial table a following query came in handy: 

select
	t.table_name,
	c.column_name
from
	information_schema.tables t
	left outer join information_schema.columns c on t.table_name = c.table_name
where
	t.table_schema = 'public' and
	t.table_name like 'vw_%' and
	c.column_name = 'wkb_geometry'

and the actual command generation

select
	'ogr2ogr -f "ESRI Shapefile" d:\temp\' || t.table_name || '.shp PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" "' || t.table_name || '"'
from
	information_schema.tables t
	left outer join information_schema.columns c on t.table_name = c.table_name
where
	t.table_schema = 'public' and
	t.table_name like 'vw_%' and
	c.column_name = 'wkb_geometry'