Re: Automatically fudging query results?

Alex Scollay wrote:
> Let's say I have a column whose values are all 2-digit integers, e.g.
> 82 (though it's actually a varchar field).
>
>> From now on, the column will be able to have 2-digit as well as 3-digit
>
> integers. In the application that uses these values, a value
> of the format x0y is considered to be the same as xy.
> E.g. values 82 and 802 are considered to be the same, 45 and 405 are
> considered to be the same, etc.
>
> Both formats still have to be supported in order to be compatible with
> historical data - I'm not in control of the database and unfortunately
> existing 2-digit data won't be converted to 3-digit.
>
> The application has many, many separate places where it reads from that
> table, e.g.
>    select colname from sometable where....
> And in many, many separate places it uses the same code (hard-coded)
> to split up each value into 2 digits, e.g. for value 82, it will
> split it up into the digits 8 and 2, and make use of them.

So the application doesn't think they're the same.

> Yep, that query and that code are scattered all over the place and are
> not in a common subroutine :( . So it would take a lot of work to change
> all of them.

You should probably correct that anyway.

> Question: Is there any way to specify the SQL query so that, when it
> sees a digit of the format xy, it automatically returns it as x0y?
> (e.g. if one row has the value 82 and another has the value 802, the SQL
> query fudges the returned rows so both of them have the value 802.)
> Maybe with regular expressions somehow?

You could write a function make_3_digits(mycol) that returns the 3 digit
version. Although you said you wanted the 2-digit version above.

> Even better, is there any way to do that on the database side without
> changing the query itself, e.g. with a trigger perhaps?

If "82" and "802" have the same meaning, but you want "802" to be used
throughout, why not just replace all the "82" values everywhere? Write a
trigger so that all new values get converted to the correct format.

Or, you could rename the base tables, replace them with views and have
those views use a function to canonicalise the format of your type.

Or, build your own type that accepts either format but always returns
the 2-digit version.

But, if you really don't have control of the database you'll have to fix
the broken application.

--
   Richard Huxton
   Archonet Ltd