Re: Problem with volatile function - Mailing list pgsql-general
From | Klint Gore |
---|---|
Subject | Re: Problem with volatile function |
Date | |
Msg-id | 4859DFB9.4090502@une.edu.au Whole thread Raw |
In response to | Problem with volatile function (Artacus <artacus@comcast.net>) |
List | pgsql-general |
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
pgsql-general by date: