/*
Distributed under BSD License, as published on
http://www.opensource.org/licenses/bsd-license.php , with the following
substitutions:

<OWNER> = Gurjeet Singh
<ORGANIZATION> = PostgreSQL Global Development Group 
<YEAR> = 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
	)
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;
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 ';
/*
	-- this code was for the case where p_columns was a text[] of column names.
	--	Deprecated since we now expect expression list in p_columns, which is
	--	simpler, performant and more flexible!
	for i in array_lower( p_columns, 1 ) .. array_upper( p_columns, 1 ) loop
		query := query || quote_ident( p_columns[i] );
		if( i <> array_upper( p_columns, 1 ) ) then
			query := query || ', ';
		end if;
	end loop;

	query := query || ' 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();
		-- raise raise notice 'Firing: %', query || (numblocks*myrand)::int || ',' || (avg_rows_per_block*myrand+1)::int || ')''';
		
		raise notice 'Requesting block number: %', (numblocks*myrand)::int;

		execute query || (numblocks*myrand)::int || ',' || (avg_rows_per_block*myrand+1)::int || ')''' 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;
