Support TABLE clause (for use with CREATE TABLE AS)

[dharrigan]

Hi,

From Slack (https://clojurians.slack.com/archives/C66EM8D5H/p1647902883353929). Original author is Nick Stares:

Hello, I'm trying to generate the following (postgres) SQL with honeysql:

CREATE TEMPORARY TABLE temp_table AS
TABLE display_impressions_reach_by_line_test
WITH NO DATA;

I've been playing around with argument orderings to create-table-as but no luck

(-> {:create-table-as [:temporary :table :temp_table :display_impressions_reach_by_line_test]}
    sql/format)
;; => ["CREATE TEMPORARY TABLE TEMP_TABLE TABLE display_impressions_reach_by_line_test AS"]

I can't figure out how I'm supposed to order the symbols in the argument vector.

[seancorfield]

TABLE display_impressions_reach_by_line_test is equivalent to SELECT * FROM display_impressions_reach_by_line_test and once that is understood, you can do this:

dev=> (sql/format {:create-table-as [:temporary :temp_table] 
                   :select :* 
                   :from :display_impressions_reach_by_line_test 
                   :with-data false})
["CREATE TEMPORARY TABLE temp_table AS SELECT * FROM display_impressions_reach_by_line_test WITH NO DATA"]

As a convenience, I'll add a :table clause.

[seancorfield]

I'm tagging this with documentation to remind me to update the create table as docs with an example using the new table clause once it is implemented.

[seancorfield]

The following will work with that change:

dev=> (sql/format {:create-table-as [:temporary :temp_table] 
                   :table :display_impressions_reach_by_line_test 
                   :with-data false})
["CREATE TEMPORARY TABLE temp_table AS TABLE display_impressions_reach_by_line_test WITH NO DATA"]