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)
