<html><head></head><body><div style="color:#000; background-color:#fff; font-family:Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:13px"><div id="yui_3_16_0_ym19_1_1518148770522_66331">Greetings all,</div><div id="yui_3_16_0_ym19_1_1518148770522_66623"><br></div><div id="yui_3_16_0_ym19_1_1518148770522_66372">I am updating the :create-table macro to work with <a class="edited-link-editor" href="http://gpdb.docs.pivotal.io/540/ref_guide/sql_commands/CREATE_TABLE.html" id="yui_3_16_0_ym19_1_1518148770522_66499">Greenplum's CREATE TABLE</a> syntax. For the most part, this is identical except for two important table attributes:</div><div id="yui_3_16_0_ym19_1_1518148770522_66919"><br></div><blockquote id="yui_3_16_0_ym19_1_1518148770522_66770"><pre id="yui_3_16_0_ym19_1_1518148770522_66636">DISTRIBUTED BY (<var id="yui_3_16_0_ym19_1_1518148770522_66637">column</var>, [ ... ] ) | DISTRIBUTED RANDOMLY</pre></blockquote><div id="yui_3_16_0_ym19_1_1518148770522_66514"><br></div><div class="enhancr-placeholder-medium" id="enhancr2_ea1f6226-5222-c24f-0c03-4d3c1490ff5c-enhancr-placeholder" data-size="medium" contenteditible="false"><div></div></div><div id="yui_3_16_0_ym19_1_1518148770522_66515">These tell Greenplum how to distribute the table across the cluster, and getting these right is critical for good performance. Here is a SQL example using the FAA dataset:</div><div id="yui_3_16_0_ym19_1_1518148770522_66769"><br></div><blockquote id="yui_3_16_0_ym19_1_1518148770522_66867"><div id="yui_3_16_0_ym19_1_1518148770522_66792" dir="ltr">create table faa.d_airports (<br id="yui_3_16_0_ym19_1_1518148770522_66822"> AirportID integer,<br id="yui_3_16_0_ym19_1_1518148770522_66823"> Name text,<br id="yui_3_16_0_ym19_1_1518148770522_66824"> City text,<br id="yui_3_16_0_ym19_1_1518148770522_66825"> Country text,<br id="yui_3_16_0_ym19_1_1518148770522_66826"> airport_code text,<br id="yui_3_16_0_ym19_1_1518148770522_66827"> ICOA_code text,<br id="yui_3_16_0_ym19_1_1518148770522_66828"> Latitude float8,<br id="yui_3_16_0_ym19_1_1518148770522_66829"> Longitude float8,<br id="yui_3_16_0_ym19_1_1518148770522_66830"> Altitude float8,<br id="yui_3_16_0_ym19_1_1518148770522_66831"> TimeZoneOffset float,<br id="yui_3_16_0_ym19_1_1518148770522_66832"> DST_Flag text ,<br id="yui_3_16_0_ym19_1_1518148770522_66833"> TZ text<br id="yui_3_16_0_ym19_1_1518148770522_66834">)<br id="yui_3_16_0_ym19_1_1518148770522_66835">distributed by (airport_code);<br></div></blockquote><div id="yui_3_16_0_ym19_1_1518148770522_66248" dir="ltr">The challenge is in emitting the 'distributed by (...)' or 'distributed randomly' <i>after </i>the closing parenthesis in the SQL. The S-SQL macro, with the correct location is:</div><div dir="ltr" id="yui_3_16_0_ym19_1_1518148770522_67036"><br></div><div dir="ltr" id="yui_3_16_0_ym19_1_1518148770522_67067">(def-sql-op :create-table (name (&rest columns) &rest options)<br id="yui_3_16_0_ym19_1_1518148770522_67057"> (when (null columns)<br id="yui_3_16_0_ym19_1_1518148770522_67058"> (sql-error "No columns defined for table ~A." name))<br id="yui_3_16_0_ym19_1_1518148770522_67059"> `("CREATE TABLE " ,(to-sql-name name) " ("<br id="yui_3_16_0_ym19_1_1518148770522_67060"> ,@(loop :for ((column-name . args) . rest) :on columns<br id="yui_3_16_0_ym19_1_1518148770522_67061"> :append (expand-table-column column-name args)<br id="yui_3_16_0_ym19_1_1518148770522_67062"> :if rest :collect ", ")<br id="yui_3_16_0_ym19_1_1518148770522_67063"> ,@(loop :for ((option . args)) :on options<br id="yui_3_16_0_ym19_1_1518148770522_67064"> :collect ", "<br id="yui_3_16_0_ym19_1_1518148770522_67065"> :append (expand-table-constraint option args))<br id="yui_3_16_0_ym19_1_1518148770522_67066"> ")"</div><div dir="ltr" id="yui_3_16_0_ym19_1_1518148770522_67099"> ; <b>DISTRIBUTED BY needs to go here</b><br></div><div dir="ltr" id="yui_3_16_0_ym19_1_1518148770522_67109">))</div><div dir="ltr" id="yui_3_16_0_ym19_1_1518148770522_67110"><br></div><div dir="ltr" id="yui_3_16_0_ym19_1_1518148770522_67111">Initially I thought I could modify expand-table-constraints in a way that would do the trick, but I could not find a way to do this without either hardcoding these two cases or modifying the function signature. Modifying the signature of the :create-table macro seems the best choice, and then add another invocation of expand-table-constraint after the closing parentheses, but that is a breaking change. Alternatively we could have a :create-gp-table, but I would rather not diverge.</div><div dir="ltr" id="yui_3_16_0_ym19_1_1518148770522_67255"><br></div><div dir="ltr" id="yui_3_16_0_ym19_1_1518148770522_67292">So, I am here to ask: am I missing something obvious? If not, what is the consensus for moving forward? I supposed I would be most in favor of modifying the signature for :create-table.</div><div dir="ltr" id="yui_3_16_0_ym19_1_1518148770522_67529"><br></div><div dir="ltr" id="yui_3_16_0_ym19_1_1518148770522_67535">Regards,</div><div dir="ltr" id="yui_3_16_0_ym19_1_1518148770522_68189"> Steve</div><div dir="ltr" id="yui_3_16_0_ym19_1_1518148770522_67471"><br></div><div dir="ltr" id="yui_3_16_0_ym19_1_1518148770522_67351">P.S. There is also an <a class="edited-link-editor" href="https://github.com/marijnh/Postmodern/issues/11" id="yui_3_16_0_ym19_1_1518148770522_67463">open issue for create table</a> that might have a need for something similar. I do not know, but it is worth considering both of them whilst making the change.<br></div><div id="yui_3_16_0_ym19_1_1518148770522_67472"><br></div><div id="yui_3_16_0_ym19_1_1518148770522_68199"><br></div></div></body></html>