Add TABLESPACE and ON COMMIT clauses to CREATE TABLE AS. ON COMMIT is

required by the SQL standard, and TABLESPACE is useful functionality.
Patch from Kris Jurka, minor editorialization by Neil Conway.

6 files changed, 106 insertions, 0 deletions

diff --git a/src/test/regress/expected/temp.out b/src/test/regress/expected/temp.out
index 897ae751bd9..c9a14fc435b 100644
--- a/src/test/regress/expected/temp.out
+++ b/src/test/regress/expected/temp.out
@@ -64,6 +64,21 @@ SELECT * FROM temptest;
 (0 rows)
 
 DROP TABLE temptest;
+BEGIN;
+CREATE TEMP TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1;
+SELECT * FROM temptest;
+ col 
+-----
+   1
+(1 row)
+
+COMMIT;
+SELECT * FROM temptest;
+ col 
+-----
+(0 rows)
+
+DROP TABLE temptest;
 -- Test ON COMMIT DROP
 BEGIN;
 CREATE TEMP TABLE temptest(col int) ON COMMIT DROP;
@@ -79,9 +94,22 @@ SELECT * FROM temptest;
 COMMIT;
 SELECT * FROM temptest;
 ERROR:  relation "temptest" does not exist
+BEGIN;
+CREATE TEMP TABLE temptest(col) ON COMMIT DROP AS SELECT 1;
+SELECT * FROM temptest;
+ col 
+-----
+   1
+(1 row)
+
+COMMIT;
+SELECT * FROM temptest;
+ERROR:  relation "temptest" does not exist
 -- ON COMMIT is only allowed for TEMP
 CREATE TABLE temptest(col int) ON COMMIT DELETE ROWS;
 ERROR:  ON COMMIT can only be used on temporary tables
+CREATE TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1;
+ERROR:  ON COMMIT can only be used on temporary tables
 -- Test foreign keys
 BEGIN;
 CREATE TEMP TABLE temptest1(col int PRIMARY KEY);
diff --git a/src/test/regress/expected/without_oid.out b/src/test/regress/expected/without_oid.out
index fbe617f9495..5e46aa8ebee 100644
--- a/src/test/regress/expected/without_oid.out
+++ b/src/test/regress/expected/without_oid.out
@@ -76,6 +76,21 @@ SELECT count(oid) FROM create_table_test2;
 -- should fail
 SELECT count(oid) FROM create_table_test3;
 ERROR:  column "oid" does not exist
+PREPARE table_source(int) AS
+    SELECT a + b AS c1, a - b AS c2, $1 AS c3 FROM create_table_test;
+CREATE TABLE execute_with WITH OIDS AS EXECUTE table_source(1);
+CREATE TABLE execute_without WITHOUT OIDS AS EXECUTE table_source(2);
+SELECT count(oid) FROM execute_with;
+ count 
+-------
+     2
+(1 row)
+
+-- should fail
+SELECT count(oid) FROM execute_without;
+ERROR:  column "oid" does not exist
 DROP TABLE create_table_test;
 DROP TABLE create_table_test2;
 DROP TABLE create_table_test3;
+DROP TABLE execute_with;
+DROP TABLE execute_without;
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index d094bd70811..9e2c3580947 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -12,6 +12,17 @@ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
 INSERT INTO testschema.foo VALUES(1);
 INSERT INTO testschema.foo VALUES(2);
 
+-- tables from dynamic sources
+CREATE TABLE testschema.asselect TABLESPACE testspace AS SELECT 1;
+SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
+    where c.reltablespace = t.oid AND c.relname = 'asselect';
+
+PREPARE selectsource(int) AS SELECT $1;
+CREATE TABLE testschema.asexecute TABLESPACE testspace
+    AS EXECUTE selectsource(2);
+SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
+    where c.reltablespace = t.oid AND c.relname = 'asexecute';
+
 -- index
 CREATE INDEX foo_idx on testschema.foo(i) TABLESPACE testspace;
 SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 42c4bc628d5..d75493fb0b6 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -13,6 +13,25 @@ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
 
 INSERT INTO testschema.foo VALUES(1);
 INSERT INTO testschema.foo VALUES(2);
+-- tables from dynamic sources
+CREATE TABLE testschema.asselect TABLESPACE testspace AS SELECT 1;
+SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
+    where c.reltablespace = t.oid AND c.relname = 'asselect';
+ relname  |  spcname  
+----------+-----------
+ asselect | testspace
+(1 row)
+
+PREPARE selectsource(int) AS SELECT $1;
+CREATE TABLE testschema.asexecute TABLESPACE testspace
+    AS EXECUTE selectsource(2);
+SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
+    where c.reltablespace = t.oid AND c.relname = 'asexecute';
+  relname  |  spcname  
+-----------+-----------
+ asexecute | testspace
+(1 row)
+
 -- index
 CREATE INDEX foo_idx on testschema.foo(i) TABLESPACE testspace;
 SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
@@ -32,6 +51,8 @@ ERROR:  tablespace "nosuchspace" does not exist
 DROP TABLESPACE testspace;
 ERROR:  tablespace "testspace" is not empty
 DROP SCHEMA testschema CASCADE;
+NOTICE:  drop cascades to table testschema.asexecute
+NOTICE:  drop cascades to table testschema.asselect
 NOTICE:  drop cascades to table testschema.foo
 -- Should succeed
 DROP TABLESPACE testspace;
diff --git a/src/test/regress/sql/temp.sql b/src/test/regress/sql/temp.sql
index 972d511ab76..6a4b8561449 100644
--- a/src/test/regress/sql/temp.sql
+++ b/src/test/regress/sql/temp.sql
@@ -66,6 +66,16 @@ SELECT * FROM temptest;
 
 DROP TABLE temptest;
 
+BEGIN;
+CREATE TEMP TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1;
+
+SELECT * FROM temptest;
+COMMIT;
+
+SELECT * FROM temptest;
+
+DROP TABLE temptest;
+
 -- Test ON COMMIT DROP
 
 BEGIN;
@@ -80,9 +90,18 @@ COMMIT;
 
 SELECT * FROM temptest;
 
+BEGIN;
+CREATE TEMP TABLE temptest(col) ON COMMIT DROP AS SELECT 1;
+
+SELECT * FROM temptest;
+COMMIT;
+
+SELECT * FROM temptest;
+
 -- ON COMMIT is only allowed for TEMP
 
 CREATE TABLE temptest(col int) ON COMMIT DELETE ROWS;
+CREATE TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1;
 
 -- Test foreign keys
 BEGIN;
diff --git a/src/test/regress/sql/without_oid.sql b/src/test/regress/sql/without_oid.sql
index 06c9c69245b..1a10a8533df 100644
--- a/src/test/regress/sql/without_oid.sql
+++ b/src/test/regress/sql/without_oid.sql
@@ -74,6 +74,18 @@ SELECT count(oid) FROM create_table_test2;
 -- should fail
 SELECT count(oid) FROM create_table_test3;
 
+PREPARE table_source(int) AS
+    SELECT a + b AS c1, a - b AS c2, $1 AS c3 FROM create_table_test;
+
+CREATE TABLE execute_with WITH OIDS AS EXECUTE table_source(1);
+CREATE TABLE execute_without WITHOUT OIDS AS EXECUTE table_source(2);
+
+SELECT count(oid) FROM execute_with;
+-- should fail
+SELECT count(oid) FROM execute_without;
+
 DROP TABLE create_table_test;
 DROP TABLE create_table_test2;
 DROP TABLE create_table_test3;
+DROP TABLE execute_with;
+DROP TABLE execute_without;