To: PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
From: Andreas Karlsson <andreas@proxel.se>
Subject: Add missing CREATE TABLE IF NOT EXISTS table_name AS EXECUTE query;
Message-ID: <2ddcc188-e37c-a0be-32bf-a56b07c3559e@proxel.se>
Date: Wed, 6 Feb 2019 03:20:41 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.4.0
MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary="------------78A8025F998FA873735A7C1E"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------78A8025F998FA873735A7C1E
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

The first example below works while the second one is a syntax error 
despite that the documentation 
(https://www.postgresql.org/docs/11/sql-createtableas.html) makes it 
seem like both should be valid. I do not see any reason to not support 
CTAS with both IF NOT EXISTS and EXECUTE at the same time so i am 
guessing that this was an oversight.

I have attached a patch which fixes this. What do you think? Should I 
add a new test case for this or is the change simple enough to not 
require any new test?

# CREATE TABLE IF NOT EXISTS a AS SELECT 1;
SELECT 1

# PREPARE q AS SELECT 1;
PREPARE
Time: 0.209 ms
foo=# CREATE TABLE IF NOT EXISTS a AS EXECUTE q;
ERROR:  syntax error at or near "EXECUTE"
LINE 1: CREATE TABLE IF NOT EXISTS a AS EXECUTE q;
                                         ^

Andreas

--------------78A8025F998FA873735A7C1E
Content-Type: application/sql;
 name="ctas-exec-ifne-v1.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="ctas-exec-ifne-v1.sql"

RnJvbSBlZmY4NDQ2YjA0ZWM2NjQwMDQ1YTRkOTJlZmVjNGNhMDllMGZmNWI0IE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBBbmRyZWFzIEthcmxzc29uIDxhbmRyZWFzQHByb3hl
bC5zZT4KRGF0ZTogV2VkLCA2IEZlYiAyMDE5IDAzOjE3OjU3ICswMTAwClN1YmplY3Q6IFtQ
QVRDSF0gQWRkIG1pc3NpbmcgSUYgTk9UIEVYSVNUIHN1cHBvcnQgZm9yIENUQVMgd2l0aCBw
cmVwYXJlZAogc3RhdG1lbnRzCgotLS0KIHNyYy9iYWNrZW5kL3BhcnNlci9ncmFtLnkgfCAx
OCArKysrKysrKysrKysrKysrKysKIDEgZmlsZSBjaGFuZ2VkLCAxOCBpbnNlcnRpb25zKCsp
CgpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvcGFyc2VyL2dyYW0ueSBiL3NyYy9iYWNrZW5k
L3BhcnNlci9ncmFtLnkKaW5kZXggYzFmYWY0MTUyYy4uNTI0YzViNzYyYSAxMDA2NDQKLS0t
IGEvc3JjL2JhY2tlbmQvcGFyc2VyL2dyYW0ueQorKysgYi9zcmMvYmFja2VuZC9wYXJzZXIv
Z3JhbS55CkBAIC0xMDY5NiwxMSArMTA2OTYsMjkgQEAgRXhlY3V0ZVN0bXQ6IEVYRUNVVEUg
bmFtZSBleGVjdXRlX3BhcmFtX2NsYXVzZQogCQkJCQljdGFzLT5pbnRvID0gJDQ7CiAJCQkJ
CWN0YXMtPnJlbGtpbmQgPSBPQkpFQ1RfVEFCTEU7CiAJCQkJCWN0YXMtPmlzX3NlbGVjdF9p
bnRvID0gZmFsc2U7CisJCQkJCWN0YXMtPmlmX25vdF9leGlzdHMgPSBmYWxzZTsKIAkJCQkJ
LyogY3JhbSBhZGRpdGlvbmFsIGZsYWdzIGludG8gdGhlIEludG9DbGF1c2UgKi8KIAkJCQkJ
JDQtPnJlbC0+cmVscGVyc2lzdGVuY2UgPSAkMjsKIAkJCQkJJDQtPnNraXBEYXRhID0gISgk
OSk7CiAJCQkJCSQkID0gKE5vZGUgKikgY3RhczsKIAkJCQl9CisJCQl8IENSRUFURSBPcHRU
ZW1wIFRBQkxFIElGX1AgTk9UIEVYSVNUUyBjcmVhdGVfYXNfdGFyZ2V0IEFTCisJCQkJRVhF
Q1VURSBuYW1lIGV4ZWN1dGVfcGFyYW1fY2xhdXNlIG9wdF93aXRoX2RhdGEKKwkJCQl7CisJ
CQkJCUNyZWF0ZVRhYmxlQXNTdG10ICpjdGFzID0gbWFrZU5vZGUoQ3JlYXRlVGFibGVBc1N0
bXQpOworCQkJCQlFeGVjdXRlU3RtdCAqbiA9IG1ha2VOb2RlKEV4ZWN1dGVTdG10KTsKKwkJ
CQkJbi0+bmFtZSA9ICQxMDsKKwkJCQkJbi0+cGFyYW1zID0gJDExOworCQkJCQljdGFzLT5x
dWVyeSA9IChOb2RlICopIG47CisJCQkJCWN0YXMtPmludG8gPSAkNzsKKwkJCQkJY3Rhcy0+
cmVsa2luZCA9IE9CSkVDVF9UQUJMRTsKKwkJCQkJY3Rhcy0+aXNfc2VsZWN0X2ludG8gPSBm
YWxzZTsKKwkJCQkJY3Rhcy0+aWZfbm90X2V4aXN0cyA9IHRydWU7CisJCQkJCS8qIGNyYW0g
YWRkaXRpb25hbCBmbGFncyBpbnRvIHRoZSBJbnRvQ2xhdXNlICovCisJCQkJCSQ3LT5yZWwt
PnJlbHBlcnNpc3RlbmNlID0gJDI7CisJCQkJCSQ3LT5za2lwRGF0YSA9ICEoJDEyKTsKKwkJ
CQkJJCQgPSAoTm9kZSAqKSBjdGFzOworCQkJCX0KIAkJOwogCiBleGVjdXRlX3BhcmFtX2Ns
YXVzZTogJygnIGV4cHJfbGlzdCAnKScJCQkJeyAkJCA9ICQyOyB9Ci0tIAoyLjExLjAKCg==
--------------78A8025F998FA873735A7C1E--