assign.sql

CREATE OR REPLACE FUNCTION assign(INT, VARCHAR(10))
  RETURNS INT AS $$
DECLARE
  total ALIAS FOR $1;
  clientname ALIAS FOR $2;
  rowcount INT;
BEGIN
  UPDATE accounts
  SET CLIENT = clientname
  WHERE id IN (
    WITH nulls AS ( -- base query
        SELECT id
        FROM accounts
        WHERE client IS NULL
    ),
        randoms AS ( -- calculate random int in range 1..count(nulls.*)
          SELECT
            i,
            trunc(random() * (count(*) - 1) + 1) :: INT random_value
          FROM nulls
            CROSS JOIN generate_series(1, total) i -- <--  total
          GROUP BY 1
      ),
        row_numbers AS ( -- add row numbers to nulls in order by id
          SELECT
            id,
            row_number()
            OVER (
              ORDER BY id) rn
          FROM nulls
      )
    SELECT id
    FROM row_numbers, randoms
    WHERE rn = random_value -- random row number
  );
  GET DIAGNOSTICS rowcount = ROW_COUNT;
  RETURN rowcount;
END;
$$ LANGUAGE plpgsql;

db> select assign(100, 'bar');
+----------+
|   assign |
|----------|
|       94 |
+----------+
SELECT 1
Command Time: 0.000s
Format Time: 0.000s
db> select count(*) from accounts where client = 'bar';
+---------+
|   count |
|---------|
|      94 |
+---------+
SELECT 1
Command Time: 0.000s
Format Time: 0.000s