Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> Hmm. All I would really need is a "macaddr to text" conversion function
> and Postgres will take care of the rest (so we could use the full string
> pattern matching capabilities). So
> SELECT m.* FROM machines m, mactbl WHERE mactbl.manuf = 'Intel'
> AND m.mac LIKE (substring(mactbl.id for 8) || '%');
Ugh. That requires applications to make assumptions about what
text-string manipulation corresponds to "extract the manufacturer part".
What's so wrong with providing a function "manufacturer(macaddr)" to
encapsulate that knowledge?
> Perhaps this is a better solution until someone complains about
> performance (since we would be going through a bunch of printf's)
Lack of indexability of the WHERE clause is going to be a much bigger
performance problem than how many printf's are involved. If you're
trying to do a lookup in a table of manufacturer codes you want to be
able to compare equality to a search value, not have to do an unanchored
LIKE comparison at every tuple.
I do like providing a macaddr-to-text conversion function (if there's
not one already), since as you say it'd allow pattern-match searches
on more general patterns than "what's the manufacturer". But extracting
the manufacturer part is a common case that is part of the agreed-on
semantics of the type, so providing a function for it seems reasonable
to me.
regards, tom lane