Re: Random multiple times

On Wed, Sep 21, 2011 at 5:43 AM, Szymon Guz <mabewlun(at)gmail(dot)com> wrote:
>
>
> On 21 September 2011 11:18, Szymon Guz <mabewlun(at)gmail(dot)com> wrote:
>>
>>
>> On 21 September 2011 10:51, Oliver Kohll - Mailing Lists
>> <oliver(dot)lists(at)gtwm(dot)co(dot)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