[Cialug] SQL wizardry possible?

[Daniel A. Ramaley]

On Tuesday 30 October 2007 16:40, Jeffrey C. Ollie wrote:
>Completely untested, but:
>
>select pref_uid,pref_value from horde_prefs where pref_name =
> 'last_login' and age(to_timestamp(to_number(substring(pref_value from
> 'i:([0-9]+);'))) < interval '6 months';

Thank you to everyone who helped so far! After learning more about SQL i 
now have a rather messy query that will give me a list of pref_uid's 
that are older than 6 months:

=> select pref_uid from horde_prefs where pref_name = 'last_login' and
   age(timestamp with time zone 'epoch' + interval '1 second' *
   to_number(substring(substring(pref_value from 's:4:"time";i:[0-9]+')
   from '[0-9]+$'), '9999999999')) > interval '6 months';

It's certainly not pretty, but i understand it, and (most importantly) 
it works. Converting from the numerical string to a timestamp could 
have been done more cleanly on a newer version of PostgreSQL, but we're 
still using 7.4.

My next challenge is for each pref_uid returned by that query, delete it 
from 3 different tables. So for example, if 'dar012' is one, then i 
need to run these 3 queries:

=> delete from turba_objects where owner_id='dar012';
=> delete from horde_histories where history_who='dar012';
=> delete from horde_prefs where pref_uid='dar012';

I suppose i could issue something like this (where <big mess> is the 
large select statement):

=> delete from turba_objects where owner_id in ( <big mess> );
=> delete from horde_histories where history_who in ( <big mess> );
=> delete from horde_prefs where pref_uid in ( <big mess> );

Of course if i went that route, deleting from horde_prefs would have to 
be last since that's what the query is pulled from. But, is there a 
more efficient way to do it, rather than running the select 3 times?

------------------------------------------------------------------------
Dan Ramaley                            Dial Center 118, Drake University
Network Programmer/Analyst             2407 Carpenter Ave
+1 515 271-4540                        Des Moines IA 50311 USA