Thread: Automatically fudging query results?
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
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
I'm a stored procedure junky and new to PostgreSQL so keep that in mind.
Here is one way you could get that logic to work. NOTE you may have to
remove the two $BODY$ peices of code to get it to compile.
Example usage select my_test('67') will return 607
select my_test('607') will return 607 WOW
MAGIC
--THIS ASSUMES YOU ARE PASSING IN A THREE DIGIT INTEGER. YOU COULD ADD
LOGIC TO HANDLE ANY LENGTH IF NEEDED
CREATE OR REPLACE FUNCTION bob.my_test(varchar)
RETURNS varchar AS
$BODY$DECLARE
v_number_in ALIAS FOR $1;
l_middle_zero VARCHAR(1);
l_first_char VARCHAR(1);
l_third_char VARCHAR(1);
l_output VARCHAR(3);
BEGIN
SELECT strpos(v_number_in,'0') INTO l_middle_zero;--this will find the
postion of the zero value. If there isn't one this will select 0 letting us
know that we need to add one
IF (l_middle_zero = 0) THEN--if a zero was not found we need to add one
SELECT substring(v_number_in,1,1) INTO l_first_char;--get the first
char of the string
SELECT substring(v_number_in,2,1)INTO l_third_char;--get the second
char of the string
l_output := l_first_char || '0' || l_third_char; --combine the
first char a zero and the last char to make our two digit number a three
digit with a zero in the middle
RETURN l_output;
END IF;
IF(l_middle_zero = 2)THEN--if the string already has the zero in the
second postion just return the number
RETURN v_number_in;
END IF;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
|---------+---------------------------------->
| | "Alex Scollay" |
| | <scollay3@hotmail.com> |
| | Sent by: |
| | pgsql-general-owner@pos|
| | tgresql.org |
| | |
| | |
| | 05/21/2004 09:27 AM |
| | |
|---------+---------------------------------->
>------------------------------------------------------------------------------------------------------------------------------|
|
|
| To: pgsql-general@postgresql.org
|
| cc:
|
| Subject: [GENERAL] Automatically fudging query results?
|
>------------------------------------------------------------------------------------------------------------------------------|
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
*************************************************************************
PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may
containproprietary, confidential and/or privileged information. If you are not the intended recipient, any use,
copying,disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient,
pleasenotify the sender immediately by return e-mail, delete this communication and destroy all copies.
*************************************************************************
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