Re: Random multiple times

On 21 September 2011 20:58, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> 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
>

Thanks :)

- Szymon