--BEGIN;

--
-- Sample function
-- Samples 'n' random records from a cursor. The cursor must be defined as a
-- scroll cursor and be opened. Do not forget to close the cursor afterwards!
--
CREATE OR REPLACE FUNCTION sample(_query TEXT, _sampleSize INT)
RETURNS SETOF RECORD
VOLATILE
AS
$BODY$
DECLARE
    _used	INT[];		-- List of used row numbers
    _count	INT;		-- Result set size
    _size	INT;		-- Sample size (eq. _count if < req. size)
    _sample	INT;		-- Current sample row number
    _record	RECORD;		-- Current sample row
    _cursor	REFCURSOR;
BEGIN
    _size := _sampleSize;

    -- Determine the number of records
    -- (MOVE FORWARD ALL isn't supported in pl/pgsql)
    OPEN _cursor FOR EXECUTE 'SELECT COUNT(*) FROM (' || _query || ') AS dummy';
    FETCH _cursor INTO _record;
    CLOSE _cursor;

    _count := _record.count;
    RAISE DEBUG 'sample: Resultset contains % rows', _count;

    -- Perform actual query
    OPEN _cursor SCROLL FOR EXECUTE _query;

    IF _count < _size THEN
	_size := _count;
    END IF;

    LOOP
	EXIT WHEN _count = 0;

	-- Get a number from 1 to _size
	_sample := round(1 + random() * (_count -1));

	-- Check we aren't finished
	EXIT WHEN array_upper(_used, 1) = _size;

	-- Check we didn't use this number yet
	RAISE DEBUG 'sample: Row number % of %', _sample, _size;
	CONTINUE WHEN _sample = ANY(_used);

	-- Mark this record as used
	_used := _used || _sample;

	-- Return the row
	FETCH ABSOLUTE _sample FROM _cursor INTO _record;

	RAISE DEBUG 'sample: Return row number %', _sample;
	RETURN NEXT _record;
    END LOOP;

    CLOSE _cursor;

    RETURN;
END;
$BODY$
LANGUAGE 'plpgsql';

-- Sample usage
SELECT *
  FROM sample(
	'SELECT table_catalog, table_schema, table_name, column_name'||
	'  FROM information_schema.columns',
	9
    ) AS (
	table_catalog	information_schema.sql_identifier,
	table_schema	information_schema.sql_identifier,
	table_name	information_schema.sql_identifier,
	column_name	information_schema.sql_identifier
    );
