Artacus wrote:
So my understanding of volatile functions is that volatile functions can
return different results given the same input.
I have a function random(int, int) that returns a random value between
$1 and $2. I want to use it in a query to generate values. But it only
evaluates once per query and not once per row like I need it to.
-- This always returns the same value
SELECT ts.sis_id, bldg_id, f_name.name, l_name.name
FROM tmp_students ts
JOIN names AS f_name ON
ts.gender = f_name.gender
WHERE f_name.counter = random(1,300)
--As does this
SELECT ts.sis_id, bldg_id, f_name.name, l_name.name
FROM tmp_students ts
JOIN names AS f_name ON
ts.gender = f_name.gender
AND ts.counter = random(1,100)
-- This generates different numbers
SELECT random(1,100), s.*
FROM usr_students s
Are you sure it's volatile? (as opposed to stable)
postgres=# create or replace function random(int,int) returns int as
$$select round($2*random()*$1)::int;$$ language sql volatile;
CREATE FUNCTION
postgres=# select generate_series from generate_series(1,10,1) where
random(1,100) > 50;
generate_series
-----------------
1
3
4
6
7
9
(6 rows)
postgres=# select generate_series from generate_series(1,10,1) where
random(1,100) > 50;
generate_series
-----------------
3
7
8
9
(4 rows)
postgres=# select random(1,100), generate_series from
generate_series(1,10,1);
random | generate_series
--------+-----------------
56 | 1
23 | 2
80 | 3
57 | 4
16 | 5
99 | 6
9 | 7
41 | 8
90 | 9
88 | 10
(10 rows)
postgres=# create or replace function random(int,int) returns int as
$$select round($2*random()*$1)::int;$$ language sql stable;
CREATE FUNCTION
postgres=# select generate_series from generate_series(1,10,1) where
random(1,100) > 50;
generate_series
-----------------
1
2
3
4
5
6
7
8
9
10
(10 rows)
postgres=# select generate_series from generate_series(1,10,1) where
random(1,100) > 50;
generate_series
-----------------
(0 rows)
postgres=# select random(1,100), generate_series from
generate_series(1,10,1);
random | generate_series
--------+-----------------
72 | 1
90 | 2
53 | 3
47 | 4
53 | 5
33 | 6
10 | 7
56 | 8
78 | 9
87 | 10
(10 rows)
postgres=#
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@xxxxxxxxxx
