Message-ID: <553B839F.2040503@gmx.net>
Date: Sat, 25 Apr 2015 14:07:59 +0200
From: Andreas <maps.on@gmx.net>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:31.0) Gecko/20100101 Thunderbird/31.6.0
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: How to drop automatically some databases?
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

I've got a developer PC (Windows) that hosts copies of a production 
database so I can tinker with it without causing damage to the 
production database.

Every night a script fetches a dump of the production database.
Then it renames the test-db on my PC, creates a new test-db and imports 
the dump.

The current version is named "db_test".
The older copies are named "db_test_yymmdd_hhmm". The name is extended 
with the timestamp of the import.

Now I'd like to keep just 3 older copies in case I want to look up 
structural moifications I've made the day before.
Older copies should get dropped.


This would show the names to drop.

select  datname
from    (
             select      datname
             from        pg_database
             where       datname ilike 'db_test_%'
             order by    datname
         )   as  x

EXCEPT
select  datname
from    (
             select      datname
             from        pg_database
             where       datname ilike 'db_test_%'
             order by    datname     desc
             limit       3
         )   as  y;


I tried to run this

drop database ( ... the select above ... )

in the DB "postgres" but it didn't work.
After a couple of seconds I get a syntax error.

What now?   :}


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin