> 2011/6/23 Álvaro Hernández <[email protected]> > > CREATE FUNCTION random_rows(table_name text, n_rnd_rows int, n_rows bigint) > > RETURNS SETOF RECORD AS $$ > > DECLARE > > curs refcursor; > > ret record; > > rnd_row_pos int[]; > > BEGIN > > FOR i IN 1..n_rnd_rows LOOP > > rnd_row_pos[i] = floor(random() * n_rows); > > END LOOP; > > > > -- Sort the array > > SELECT INTO rnd_row_pos array_agg(i) FROM (SELECT * FROM > > unnest(rnd_row_pos) i ORDER BY i) AS i; > > > > OPEN curs FOR EXECUTE 'SELECT * FROM ' || table_name; -- > > ORDER is not relevant > > > > MOVE ABSOLUTE rnd_row_pos[1] - 1 FROM curs; > > FETCH NEXT FROM curs INTO ret; > > RETURN NEXT ret; > > > > FOR i IN 2..n_rnd_rows LOOP > > MOVE RELATIVE rnd_row_pos[i] - rnd_row_pos[i - 1] - > > 1 FROM curs; > > FETCH NEXT FROM curs INTO ret; > > RETURN NEXT ret; > > END LOOP;
El problema de esta idea es que cada MOVE y FETCH requieren leer todas las tuplas intermedias para contarlas. Capturar una tupla que está hacia el final de la tabla es muy costoso. -- Álvaro Herrera <[email protected]> - Enviado a la lista de correo pgsql-es-ayuda ([email protected]) Para cambiar tu suscripci�n: http://www.postgresql.org/mailpref/pgsql-es-ayuda
