I remember putting something like that together. It's in the contrib directory of the trunk. But I'll attach a copy here.
Don't filter on the datefield table. It represents the Date: header of a message, not the received date. The physmessage.internal_date represents the received date. Which is most likely what you want. Eric Hiller wrote: > I have a spam filter that works pretty good, only problem is most users > are too lazy to click purge and actually clean out their 'Trash' folder > and then they call and complain that their quote is exceeded. So I put > together this little query below to solve that problem, it will delete > the mail in the trash folder after 1 day when run. I am just going to > put it into a cron job. > > I just wanted to A) check and make sure this looked correct to a fresh > pair of eyes and B) give this to anyone else it might be useful for. > > UPDATE dbmail_messages SET status=2 WHERE physmessage_id NOT IN (SELECT > physmessage_id FROM dbmail_datefield WHERE current_timestamp at time > zone 'GMT' - '1 day'::interval <= datefield) AND mailbox_idnr IN (SELECT > mailbox_idnr FROM dbmail_mailboxes WHERE name='Trash'); > > > > Thanks, > Eric > > ------------------------------------------------------------------------ > Your smile counts. The more smiles you share, the more we donate. Join > in! <www.windowslive.com/smile?ocid=TXT_TAGLM_Wave2_oprsmilewlhmtagline> > > > ------------------------------------------------------------------------ > > _______________________________________________ > DBmail mailing list > [email protected] > https://mailman.fastxs.nl/mailman/listinfo/dbmail -- ________________________________________________________________ Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlands________________________________http://www.nfg.nl
expiretrash.sh
Description: application/shellscript
_______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
