Modify backreference using function in postgresql

Question

I want to add (int) value to a backreference.

For this I created a function and pass the appropriate backreference. The backreference if returned without any modification works fine, however when I try to modify or use any other functions on the backreference that was passed it assumes \3 as the argument value and not the backreference value itself.

For eg-

CREATE OR REPLACE FUNCTION add10(text) returns text as $$
DECLARE
    t int;
BEGIN   
    t := to_number($1, '999999') + 10;
    return trim(to_char(t, '999999'), ' ');
END;
$$ LANGUAGE plpgsql;

then:

select regexp_replace('890808', '80(\d+)', add10('\1'), 'g');

should give result as

 test
-------
 89018
(1 row)

However it gives --

 test
-------
 89011
(1 row)

taking the value of $1 as 1(the backreference number) instead of value 8.

Any ideas why does this happen?

And where does the add10() function enter?

Patrick
– Patrick

2014-04-06 04:38:15 +00:00
Commented Apr 6, 2014 at 4:38 — Patrick
– Patrick, Commented Apr 6, 2014 at 4:38

Craig Ringer · Accepted Answer · 2014-04-06 04:48:33Z

Problem: order of evaluation

My guess (and only a guess, given that the question isn't super clear) is that you're confused by the order of evaluation of arguments within function calls, and are trying to call a function on a backref value, but order of evaluation means that it's called on the backref string before regexp evaluation.

Assuming that add10 and t are the same thing, then:

select regexp_replace('890808', '80(\d+)', add10('\1'), 'g');

is evaluated by first calling add10('\1'). That will in turn run:

select to_number('\1', '999999') + 10 into t;

Since select to_number('\1', '999999') produces the value 1, you'll get 11 in t. You then convert that back to a string (via a rather weird approach, why didn't you just cast it).

So you've replaced '\1' with '11', so your regexp_replace call looks like:

select regexp_replace('890808', '80(\d+)', '11`, 'g');

... from which you can see where your unexpected result came from.

Solution: Split the value, modify it, then reassemble it

I don't think your desired result makes any sense, so I can't really figure out how to produce it. You seem to be trying to retain all digits before "80", discard "80", convert all digits after the "80" to a number and add 10, then substitute it back in. Which is pretty WTFy, why?

Regular expressions are one way to split numbers up, but the best way is usually modulus and remainder:

craig=> SELECT 890808 / 10000, 890808 % 10000;
 ?column? | ?column? 
----------+----------
       89 |      808
(1 row)

If you must use regexps (say, if it's mixed alphanumeric or if your criteria are not easily expressed by place values), you probably want to use regexp_split_to_array.

+1 for making sense of something that had little sense to begin with

Collectives™ on Stack Overflow

Modify backreference using function in postgresql

1 Answer 1

Problem: order of evaluation

Solution: Split the value, modify it, then reassemble it

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Problem: order of evaluation

Solution: Split the value, modify it, then reassemble it

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related