contrib/tsm_system_rows/expected/tsm_system_rows.out

CREATE EXTENSION tsm_system_rows;
CREATE TABLE test_tablesample (id int, name text);
INSERT INTO test_tablesample SELECT i, repeat(i::text, 1000)
  FROM generate_series(0, 30) s(i);
ANALYZE test_tablesample;
SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (0);
 count 
-------
     0
(1 row)

SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (1);
 count 
-------
     1
(1 row)

SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (10);
 count 
-------
    10
(1 row)

SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (100);
 count 
-------
    31
(1 row)

-- bad parameters should get through planning, but not execution:
EXPLAIN (COSTS OFF)
SELECT id FROM test_tablesample TABLESAMPLE system_rows (-1);
               QUERY PLAN               
----------------------------------------
 Sample Scan on test_tablesample
   Sampling: system_rows ('-1'::bigint)
(2 rows)

SELECT id FROM test_tablesample TABLESAMPLE system_rows (-1);
ERROR:  sample size must not be negative
-- fail, this method is not repeatable:
SELECT * FROM test_tablesample TABLESAMPLE system_rows (10) REPEATABLE (0);
ERROR:  tablesample method system_rows does not support REPEATABLE
LINE 1: SELECT * FROM test_tablesample TABLESAMPLE system_rows (10) ...
                                                   ^
-- but a join should be allowed:
EXPLAIN (COSTS OFF)
SELECT * FROM
  (VALUES (0),(10),(100)) v(nrows),
  LATERAL (SELECT count(*) FROM test_tablesample
           TABLESAMPLE system_rows (nrows)) ss;
                        QUERY PLAN                        
----------------------------------------------------------
 Nested Loop
   ->  Values Scan on "*VALUES*"
   ->  Aggregate
         ->  Sample Scan on test_tablesample
               Sampling: system_rows ("*VALUES*".column1)
(5 rows)

SELECT * FROM
  (VALUES (0),(10),(100)) v(nrows),
  LATERAL (SELECT count(*) FROM test_tablesample
           TABLESAMPLE system_rows (nrows)) ss;
 nrows | count 
-------+-------
     0 |     0
    10 |    10
   100 |    31
(3 rows)

CREATE VIEW vv AS
  SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (20);
SELECT * FROM vv;
 count 
-------
    20
(1 row)

DROP EXTENSION tsm_system_rows;  -- fail, view depends on extension
ERROR:  cannot drop extension tsm_system_rows because other objects depend on it
DETAIL:  view vv depends on function system_rows(internal)
HINT:  Use DROP ... CASCADE to drop the dependent objects too.