[Cialug] SQL wizardry possible?

[Carl Olsen]

That's what I was thinking - a user defined function.  A serialized array
should be fun to work with.  I've written a basic string formatting function
before, but nothing like a function that operates on a serialized array.

Carl

-----Original Message-----
From: cialug-bounces at cialug.org [mailto:cialug-bounces at cialug.org] On Behalf
Of David Champion
Sent: Tuesday, October 30, 2007 4:38 PM
To: Central Iowa Linux Users Group
Subject: Re: [Cialug] SQL wizardry possible?

You should shoot the person that designed that database. I worked on a
system that stored data similar to that - they had data fields as long
strings of pipe delimited data, apparently because an earlier version of
dBase had a small field count limit, and they needed more data elements
per row. It made things very painful to work with, until we did a
re-write and separated the data out.

But... I'd guess you'll have to use some kind of user-defined function,
or copy the time stamp out into a separate field to do what you want.

-dc

Daniel A. Ramaley wrote:
> There is a rather complex SQL (specifically, PostgreSQL) query that i 
> want to do. I don't know if it is possible with pure SQL.
> 
> If i run this query:
> select pref_uid,pref_value from horde_prefs where pref_name =
'last_login';
> 
> I get back many rows of output. Here's a sample of the pref_uid and 
> pref_value data:
> 
>   pref_uid: dar012
> pref_value: a:2:{s:4:"time";i:1193777972;s:4:"host";s:11:"10.11.3.162";}
> 
> What i want to do it pull out the time stamp that is embedded in 
> pref_value. In this example it is "1193777972". And then based on that 
> time stamp, i want to only return records if the time stamp is less 
> than some other time stamp (now minus 6 months, or roughly 1178226993 at 
> the time of this writing). Is it possible with SQL to pull out a 
> substring and then do a comparison on it?
> 
> If it is not possible with pure SQL, then i already know how to do 
> something with Perl, i'm just trying to find a more elegant way that 
> doesn't mix as many languages.
> 
> ------------------------------------------------------------------------
> Dan Ramaley                            Dial Center 118, Drake University
> Network Programmer/Analyst             2407 Carpenter Ave
> +1 515 271-4540                        Des Moines IA 50311 USA
> _______________________________________________
> Cialug mailing list
> Cialug at cialug.org
> http://cialug.org/mailman/listinfo/cialug
> 


_______________________________________________
Cialug mailing list
Cialug at cialug.org
http://cialug.org/mailman/listinfo/cialug