On 21 September 2011 20:58, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
very clever.On Wed, Sep 21, 2011 at 5:43 AM, Szymon Guz <mabewlun@xxxxxxxxx> wrote:
>
>
> On 21 September 2011 11:18, Szymon Guz <mabewlun@xxxxxxxxx> wrote:
>>
>>
>> On 21 September 2011 10:51, Oliver Kohll - Mailing Lists
>> <oliver.lists@xxxxxxxxxx> 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
merlin
Thanks :)
- Szymon
