Re: Automatically fudging query results? - Mailing list pgsql-general

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.
>
> 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.
>
> 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?
>
> Even better, is there any way to do that on the database side without
> changing the query itself, e.g. with a trigger perhaps?
>
> _________________________________________________________________
> The new MSN 8: advanced junk mail protection and 2 months FREE*
> http://join.msn.com/?page=features/junkmail
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html

temp=# create table temp (string1 varchar(8)) without oids;
CREATE TABLE
temp=# insert into temp values ('82');
INSERT 0 1
temp=# insert into temp values ('802');
INSERT 0 1
temp=# select * from temp;
 string1
---------
 82
 802
(2 rows)

temp=# select string1, case when char_length(string1)=3 then string1
when char_length(string1)=2 then substring(string1 from 1 for 1) || '0'
|| substring(string1 from 2 for 1) end from temp;
 string1 | case
---------+------
 82      | 802
 802     | 802

Now you could wrap this lot up in a view named the same as the original
table...

temp=# create table temp_table (string1 varchar(8)) without oids;
CREATE TABLE
temp=# insert into temp values ('82');
INSERT 0 1
temp=# insert into temp values ('802');
INSERT 0 1
temp=#  create view temp AS select case when char_length(string1)=3 then
string1 when char_length(string1)=2 then substring(string1 from 1 for 1)
|| '0' || substring(string1 from 2 for 1) end as string1 from temp_table;
CREATE VIEW
temp=# select * from temp;
 string1
---------
 802
 802



Hope thats almost clear


Nick