# Update rows in table with set-returning function

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.?

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

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.