5
\$\begingroup\$

We all know and love the corpulent DDL expressions one can write in PostgreSQL with wording like DEFERRABLE INITIALLY DEFERRED, ALTER FOREIGN DATA WRAPPER, or even DROP TEXT SEARCH CONFIGURATION. But how far can one go? Here comes the definitive challenge for the longest DDL expression, with constraints of course. Mwahahaha!

Rules:

  • The length of the submission is counted in Unicode codepoints.
  • Whitespaces are contracted to minimally necessary when counting.
  • The semicolon counts.
  • The submission must be a valid data definition expression in PostgreSQL 12 as described in the relevant manual section.
  • Optionally repeatable subexpressions (like column definitions in a CREATE TABLE) must not be repeated.
  • Every user-specifiable identifier only adds 1 towards the total length. So a table named a is worth the same as aaa.
  • Function call expressions add exactly 4 towards the total length, as if they were always of the form f(x).
  • String constants only add as much towards the total length as if they were empty strings.
  • Numeric constants always add exactly 1 towards the total length, as if all you had were single digit integers.
  • Recursion is not allowed. For example, the SQL SELECT statement defines the concept of a from_item recursively, where a from_item can be of the form from_item [...] join_type [...] from_item. This is a recursive definition, and its usage is forbidden. This challenge disallows terms embedded in the same type of term, in any depth.

Good luck, have fun. To kickstart your thoughts, here's a beatable example of length 106:

  create function f(x)
  returns table(a float) as $$$$
  language sql
  rows 1
  immutable
  parallel safe
  leakproof;
Improve this question
\$\endgroup\$
5
  • 3
    \$\begingroup\$ Is it really impossible to get a infinitely extensible statement? \$\endgroup\$ Commented Apr 17, 2020 at 3:46
  • \$\begingroup\$ Could you be a little bit more specific as to what you mean when you say recursion is banned? All of your other rules are very unambiguous in what they mean, but I feel like this on is a lot more edge casey. Could you lay out in a way similar to your other rules what exactly counts as recursion? \$\endgroup\$ Commented Apr 17, 2020 at 4:29
  • \$\begingroup\$ I've edited the recursion rule, hopefully it makes more sense now. Can you please check @AdHocGarfHunter whether you understand it now? \$\endgroup\$ Commented Apr 17, 2020 at 13:02
  • \$\begingroup\$ are subqueries allowed? \$\endgroup\$ Commented Apr 17, 2020 at 13:07
  • \$\begingroup\$ Sure, everything is allowed what's not forbidden. But take note - this challenge is specifically targeted to DDL queries. A SELECT in itself is a DML query. \$\endgroup\$ Commented Apr 17, 2020 at 21:08

1 Answer 1

Reset to default
2
\$\begingroup\$

PostgreSQL 12, CREATE TABLE, 482 chars:

CREATE GLOBAL TEMPORARY TABLE IF NOT EXISTS table_name 
PARTITION OF parent_table (column_name WITH OPTIONS CONSTRAINT constraint_name REFERENCES reftable (refcolumn) MATCH PARTIAL ON DELETE referential_action ON UPDATE referential_action NOT DEFERRABLE INITIALLY IMMEDIATE CONSTRAINT constraint_name 
EXCLUDE USING index_method (column_name opclass DESC NULLS FIRST WITH operator) INCLUDE (column_name) WITH (storage_parameter = value) USING INDEX TABLESPACE tablespace_name WHERE (predicate) NOT DEFERRABLE INITIALLY IMMEDIATE) FOR VALUES WITH (MODULUS numeric_literal, REMAINDER numeric_literal) PARTITION BY RANGE (column_name COLLATE collation opclass) USING method WITHOUT OIDS ON COMMIT PRESERVE ROWS 
TABLESPACE tablespace_name;

How?

To produce the largest possible DDL query in PostgreSQL 12, I wrote a script that does the following:

  1. Navigates to the full list of PostgreSQL 12 commands.
  2. For each command, extracts its synopsis.
  3. Parses the synopsis into an Abstract Syntax Tree.
  4. Traverses the Abstract Syntax Tree, producing all queries, pruning branches for rudimentary optimization by choosing the longest keywords.
  5. For the generated queries, finds the maximum by condensing non-keyword identifiers down to a single character, eliminating unnecessary whitespace, etc.
Improve this answer
\$\endgroup\$
1
  • 1
    \$\begingroup\$ it is... beautiful... truly a marvel to behold \$\endgroup\$ Commented Mar 21, 2022 at 10:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.