Automatically fudging query results?

[Alex Scollay]

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

[Nick Barr]

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(str ing1)=3 then string1
when char_length(str ing1)=2 then substring(strin g1 from 1 for 1) || '0'
|| substring(strin g1 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(str ing1)=3 then
string1 when char_length(str ing1)=2 then substring(strin g1 from 1 for 1)
|| '0' || substring(strin g1 from 2 for 1) end as string1 from temp_table;
CREATE VIEW
temp=# select * from temp;
string1
---------
802
802

Hope thats almost clear
Nick


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Richard Huxton]

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(m ycol) 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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org