what i read is different from what you expect, what actually happened:
it didn't return the same digit each time. instead,
it returned one digit once only, as i would expect that the trunc(random() * 9 + 1)::text to be evaluated once only.
the next the query did was replacing the all the digit with the one random digit (converted to text). the 'g' option indicates the greedy, which replace everything with the digit.
you should loop through number digits and invoke the replace for each digit to get your expected result instead.
---
daniel baktiar
On Wed, Sep 21, 2011 at 16:51, Oliver Kohll - Mailing Lists
<oliver.lists@gtwm.co.uk> wrote:
Hi,
I understand random() is a volatile function and runs multiple times for multiple rows returned by a SELECT, however is there a way of getting it to run multiple times *within* another function call and in the same row. i.e. something like
select regexp_replace('+1 555 555 555', E'\\d', trunc(random() * 9 + 1)::text,'g');
regexp_replace
----------------
+1 111 111 111
(1 row)
As you can see, it returns the same digit each time. I've tried wrapping a select around the trunc too.
Regards
Oliver Kohll
www.gtwm.co.uk / www.agilebase.co.uk
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general