Fix support for CREATE TABLE IF NOT EXISTS AS EXECUTE

The grammar IF NOT EXISTS for CTAS is supported since 9.5 and documented
as such, however the case of using EXECUTE as query has never been
covered as EXECUTE CTAS statements and normal CTAS statements are parsed
separately.

Author: Andreas Karlsson
Discussion: https://postgr.es/m/2ddcc188-e37c-a0be-32bf-a56b07c3559e@proxel.se
Backpatch-through: 9.5

diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 1ab94931f3cf64fcc64fa64ce5f0129fb5ba3d31..9d652a3e1006488c2b107a1c3ce2dd81b6fb92ae 100644 (file)
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10773,11 +10773,29 @@ ExecuteStmt: EXECUTE name execute_param_clause
                    ctas->into = $4;
                    ctas->relkind = OBJECT_TABLE;
                    ctas->is_select_into = false;
+                   ctas->if_not_exists = false;
                    /* cram additional flags into the IntoClause */
                    $4->rel->relpersistence = $2;
                    $4->skipData = !($9);
                    $$ = (Node *) ctas;
                }
+           | CREATE OptTemp TABLE IF_P NOT EXISTS create_as_target AS
+               EXECUTE name execute_param_clause opt_with_data
+               {
+                   CreateTableAsStmt *ctas = makeNode(CreateTableAsStmt);
+                   ExecuteStmt *n = makeNode(ExecuteStmt);
+                   n->name = $10;
+                   n->params = $11;
+                   ctas->query = (Node *) n;
+                   ctas->into = $7;
+                   ctas->relkind = OBJECT_TABLE;
+                   ctas->is_select_into = false;
+                   ctas->if_not_exists = true;
+                   /* cram additional flags into the IntoClause */
+                   $7->rel->relpersistence = $2;
+                   $7->skipData = !($12);
+                   $$ = (Node *) ctas;
+               }
        ;
 
 execute_param_clause: '(' expr_list ')'                { $$ = $2; }

diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 8cd148601d29a096ccc51a8f284e492d7de6b049..d2ee3f4035a07853ba3448deae725b3958acb336 100644 (file)
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -263,6 +263,20 @@ ERROR:  relation "as_select1" already exists
 CREATE TABLE IF NOT EXISTS as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
 NOTICE:  relation "as_select1" already exists, skipping
 DROP TABLE as_select1;
+PREPARE select1 AS SELECT 1 as a;
+CREATE TABLE as_select1 AS EXECUTE select1;
+CREATE TABLE as_select1 AS EXECUTE select1;
+ERROR:  relation "as_select1" already exists
+SELECT * FROM as_select1;
+ a 
+---
+ 1
+(1 row)
+
+CREATE TABLE IF NOT EXISTS as_select1 AS EXECUTE select1;
+NOTICE:  relation "as_select1" already exists, skipping
+DROP TABLE as_select1;
+DEALLOCATE select1;
 -- create an extra wide table to test for issues related to that
 -- (temporarily hide query, to avoid the long CREATE TABLE stmt)
 \set ECHO none

diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index df8b66fac4d54cc6f7257e130730c05ca058b7c0..f61cd595f765b6dd5759ff4f51732daa1ca2e7e8 100644 (file)
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -278,6 +278,14 @@ CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
 CREATE TABLE IF NOT EXISTS as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
 DROP TABLE as_select1;
 
+PREPARE select1 AS SELECT 1 as a;
+CREATE TABLE as_select1 AS EXECUTE select1;
+CREATE TABLE as_select1 AS EXECUTE select1;
+SELECT * FROM as_select1;
+CREATE TABLE IF NOT EXISTS as_select1 AS EXECUTE select1;
+DROP TABLE as_select1;
+DEALLOCATE select1;
+
 -- create an extra wide table to test for issues related to that
 -- (temporarily hide query, to avoid the long CREATE TABLE stmt)
 \set ECHO none