/* Distributed under BSD License, as published on http://www.opensource.org/licenses/bsd-license.php , with the following substitutions: = Gurjeet Singh = PostgreSQL Global Development Group = 2008 */ create or replace function get_random_rows ( p_schemaname name, p_tablename name, p_columns text, p_where text, p_numrows bigint, -- count(*)'s datatype p_maxretries bigint, -- count(*)'s datatype p_sequence text -- should've been 'name', but ::regclass coesn't accept name datatype. ) returns setof record as $$ declare i int; misses bigint; -- count(*)'s datatype colname name; ret record; query text; finalquery text; numblocks pg_class.relpages%type; avg_rows_per_block int; myrand double precision; block_no bigint; -- datatype of BlockNumber is uint32 and that of a sequence is bigint row_no int; begin select relpages, (reltuples/relpages)::int into numblocks, avg_rows_per_block from pg_catalog.pg_class as c where (p_schemaname is not null and c.relnamespace = (select oid from pg_namespace where nspname = p_schemaname) ) or (p_schemaname is null and c.relnamespace in (select oid from pg_namespace where nspname in (select arr[s] from ( select arr, generate_series( array_lower( arr, 1 ), array_upper( arr, 1 ) ) as s from (select string_to_array( regexp_replace( setting, E'"\\$user"', (select user) ), ',' ) as arr from pg_settings where name = 'search_path') as a ) as a))) and relname = p_tablename and relkind = 'r'; if( numblocks is null ) then raise exception 'Table [%.]% does not exist.', p_schemaname, p_tablename; end if; query := 'select ' || p_columns || ' from '; if( p_schemaname is not null ) then query := query || quote_ident( p_schemaname ) || '.'; end if; query := query || quote_ident( p_tablename ) || ' where '; if( p_where is not null ) then query := query || ' ( ' || p_where || ' ) and '; end if; query := query || ' ctid = ''('; misses := 0; i := 1; while( i <= p_numrows ) loop myrand := random(); block_no := nextval( p_sequence::regclass ); if( block_no >= numblocks ) then block_no := 0; perform setval( p_sequence::regclass, 0 ); end if; row_no := (avg_rows_per_block*myrand+1)::int; -- raise raise notice 'Firing: %', query || (numblocks*myrand)::int || ',' || (avg_rows_per_block*myrand+1)::int || ')'''; -- raise notice 'Requesting block,row number: %,%', block_no, row_no; execute query || block_no || ',' || row_no || ')''' into ret; if( ret is not null ) then return next ret; i := i + 1; else -- raise notice 'Missed: %', query || (numblocks*myrand)::int || ',' || (avg_rows_per_block*myrand+1)::int || ')'''; misses := misses + 1; if( p_maxretries is not null and misses >= p_maxretries ) then raise notice 'Reached limit on number of misses: %', p_maxretries; exit; -- the loop end if; end if; end loop; if( misses > 0 ) then raise notice 'Number of misses: %', misses; end if; end; $$ language plpgsql;