From: "Greg Sabino Mullane" <greg@turnstep.com>
To: pgsql-sql@postgresql.org
Subject: Re: compare table names
In-Reply-To: <1326126523.1983.16.camel@tony1.localdomain>
Date: Mon,  9 Jan 2012 16:51:30 -0000
Message-ID: <a2359afd600c75322c5279728a04b8c3@biglumber.com>


-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> tmp_staging1229
>
> This query is part of a larger script where I want to dynamically select
> tablenames older than 10 days and drop them.  The tables are created in
> a tmp_stagingMMDD format.  I know postgres does not maintain object
> create times, how can I write this to select tables from pg_tables that
> are older than 10 days?

First, be aware that MMDD alone is a suboptimal choice, for you will get 
burned by year boundaries, unless you go to crazy efforts to look at 
the current year, devine if 1230 should be 'less' than 0102 because 
it's January, etc. Assuming you change it to YYYYMMDD, you could run 
a simple query like this:

SELECT tablename
FROM pg_tables
WHERE tablename ~ '^tmp_staging'
AND substring(tablename from '\d+')::date < now() - '10 days'::interval;

- -- 
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201201091144
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk8LGuUACgkQvJuQZxSWSshD0QCcDipiHcgchfQMHMC6jC9ExkCv
K44Anjy7eRg0uVNOoZ3AbHecf1nn6TmT
=v/9C
-----END PGP SIGNATURE-----