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

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. 
*************************************************************************