Re: [GENERAL] Random multiple times
Many thanks both, those solutions are great and have gone in my wiki for future ref. Regards Oliver On 21 Sep 2011, at 21:56, Szymon Guz wrote: Short answer is: yes. More information you can find here http://simononsoftware.com/problem-with-random-in-postgresql-subselect/ regards Szymon
[GENERAL] Random multiple times
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
Re: [GENERAL] Random multiple times
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
Re: [GENERAL] Random multiple times
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.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Random multiple times
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
Re: [GENERAL] Random multiple times
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Random multiple times
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
