Update rows in table with set-returning function

Question

I am trying to update a table of 100 already existing rows with 100 random values from a Gaussian distribution, for which I am using the normal_rand function from the tablefunc extension. Since this is a set-returning function I placed it it the FROM-clause.

UPDATE foo
SET bar = r
FROM normal_rand(100, 0, 1) AS r;

However, when executing the statement, all rows in the table are assigned the same value (presumably the first), as such:

bar
-------
0.2451
0.2451
0.2451
...
0.2451

I also tried generate_series, but this yields similar results. How can I assign the first value of normal_rand(100, 0, 1) to the first row, the second to the second etc.?

Answer 1

This is not going to be fast, but you need a subquery that assigns row numbers for each row that can be used for joining the generated values with the table. You can use with ordinality to generate such number for the result of a set-returning function.

with numbered as (
 select id, 
 row_number() over (order by id) as rn
 from foo 
)
update foo
 set r = t.val
from (
 select n.id,
 nr.val
 from numbered n
 join normal_rand(1000, 5, 3) with ordinality nr(val,idx) on n.rn = nr.idx
) t
where t.id = foo.id

Replace id with the primary key column of your table.

Online example

Answer 2

The cleanest way may be to wrap the set returning function into a scalar function:

create function normal() 
returns double precision volatile 
language SQL 
as 
$$ 
 select * from normal_rand(1, 0, 1) 
$$;
UPDATE foo SET bar = normal();

Gaussian values are computed in pairs, and this throws away one of the values from each pair, so is slightly inefficient. But that inefficiency is small compared to whatever other contortions you would have to go through to make it work with the unwrapped set returning function.

• Jens de Bruijn: I am wondering why Gaussian values are computed in pairs?
• jjanes: That is just how the Box-Muller algorithm works (en.wikipedia.org/wiki/Box%E2%80%93Muller_transform). You get two for the same price as one.