Would this work? I am trying to get it down to a single statement.
UPDATE dbmail_messages SET status=2 WHERE physmessage_id NOT IN (SELECT id FROM
dbmail_physmessage WHERE current_timestamp at time zone 'GMT' - '1
day'::interval <= internal_date) AND mailbox_idnr IN (SELECT mailbox_idnr FROM
dbmail_mailboxes WHERE name IN ('Trash','INBOX/Trash') OR name LIKE 'Trash/%'
OR name LIKE 'INBOX/Trash/%') ORDER BY physmessage_id;
Should I also set deleted_flag=1 as you did in your script?
Thanks for the help!
> Date: Wed, 5 Dec 2007 21:53:58 +0100
> From: [EMAIL PROTECTED]
> To: [email protected]
> Subject: Re: [Dbmail] automatic purgeing of trash folder
>
> 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
_________________________________________________________________
Share life as it happens with the new Windows Live.Download today it's FREE!
http://www.windowslive.com/share.html?ocid=TXT_TAGLM_Wave2_sharelife_112007
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
