summaryrefslogtreecommitdiffstats
path: root/contrib/tsm_system_rows/sql
diff options
context:
space:
mode:
Diffstat (limited to 'contrib/tsm_system_rows/sql')
-rw-r--r--contrib/tsm_system_rows/sql/tsm_system_rows.sql39
1 files changed, 39 insertions, 0 deletions
diff --git a/contrib/tsm_system_rows/sql/tsm_system_rows.sql b/contrib/tsm_system_rows/sql/tsm_system_rows.sql
new file mode 100644
index 0000000..e3ab420
--- /dev/null
+++ b/contrib/tsm_system_rows/sql/tsm_system_rows.sql
@@ -0,0 +1,39 @@
+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);
+SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (1);
+SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (10);
+SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (100);
+
+-- bad parameters should get through planning, but not execution:
+EXPLAIN (COSTS OFF)
+SELECT id FROM test_tablesample TABLESAMPLE system_rows (-1);
+
+SELECT id FROM test_tablesample TABLESAMPLE system_rows (-1);
+
+-- fail, this method is not repeatable:
+SELECT * FROM test_tablesample TABLESAMPLE system_rows (10) REPEATABLE (0);
+
+-- 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;
+
+SELECT * FROM
+ (VALUES (0),(10),(100)) v(nrows),
+ LATERAL (SELECT count(*) FROM test_tablesample
+ TABLESAMPLE system_rows (nrows)) ss;
+
+CREATE VIEW vv AS
+ SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (20);
+
+SELECT * FROM vv;
+
+DROP EXTENSION tsm_system_rows; -- fail, view depends on extension