Re: Problem with volatile function

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@une.edu.au