On 21 September 2011 20:58, Merlin Moncure <mmonc...@gmail.com> wrote:
> On Wed, Sep 21, 2011 at 5:43 AM, Szymon Guz <mabew...@gmail.com> wrote: > > > > > > On 21 September 2011 11:18, Szymon Guz <mabew...@gmail.com> wrote: > >> > >> > >> On 21 September 2011 10:51, Oliver Kohll - Mailing Lists > >> <oliver.li...@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 > >>> > >>> > >>> > >> > >> Short answer is: yes. More information you can find > >> here > http://simononsoftware.com/problem-with-random-in-postgresql-subselect/ > >> regards > >> Szymon > >> > > > > Sorry for the previous answer, this is not correct answer to your > problem... > > try this one: > > with splitted as ( > > select regexp_split_to_table('+1 555 555 555', '') as x > > ) > > select > > array_to_string( > > array_agg( > > regexp_replace(x, E'\\d', trunc(random()*9 + 1)::text, 'g') > > ), > > '') > > from splitted; > > The problem was that in your query the function was called once (for > > creating the params of the function regexp_replace, you had there only > one > > call of this function, so random() was also called once. > > In my query the regexp is called for each char from the input string. > > regards > > Szymon > > very clever. > > merlin > Thanks :) - Szymon