Re: Problem with volatile function

> You can force Pg to re-evaluate random() by adding a dummy parameter
> that depends on the input record, or (probably better) by writing a
> variant of it that tests the input against a randomly generated value
> and returns a boolean. Eg:
>
>
Thanks all. So here's the situation. I added a dummy parameter and
passed the id like you suggested. That had no effect. I still got one
name for males and one name for females. So I used the 3rd param in a
trivial way:

    select round($2*random()*$1+($3/10000000))::int;

And that actually forced it to execute for every row. However, it
returned unpredictable results. There should have been only one match
for first_name and last_name for each person but it return from 1 to 5
rows for each person.

 sis_id     gender     name        name        counter     counter
 ---------  ---------  ----------  ----------  ----------  ----------
 105607     M          Denis       Weber       19          671
 105666     M          Javier      Custodio    154         182
 105666     M          Javier      Nelson      154         250
 105839     M          Johnnie     Whicker     295         32
 105847     F          Trina       Garcia      259         155
 105847     F          Dione       Freeman     103         651
 105847     F          Dione       Harden      103         897
 105847     F          Cruz        Brannen     249         1240

So what I actually had to do was get the sis_id and the two random
numbers in a subselect.

SELECT stu.sis_id, stu.gender, f_name.name AS first_name, l_name.name AS
last_name
FROM usr_students stu
JOIN (
    SELECT sis_id, random(1,300) AS f_cnt, random(1,1700) AS l_cnt
    FROM usr_students s
) sub ON stu.sis_id = sub.sis_id
JOIN names f_name ON stu.gender = f_name.gender
   AND f_name.counter = sub.f_cnt
JOIN names l_name ON l_name.gender IS NULL
   AND l_name.counter = sub.l_cnt

So while that works, postgres isn't behaving how I'd expect (or how Tom
expects from the sounds of it)