The source code inside blocks are treated as templates which get preprocessed before passing to the host language interpreter.
Inside templates users are able to use control flow constructs, like loops and conditions and interpolation expressions. Interpolation expressions within templates are evaluated and their string representation is automatically inserted at the position. If an interpolation expression evaluates to null an empty string is added.
Expressions
Expressions insert value of the CoginitiScript expression into a final code. {{ and }} delimiters are used to insert an expression result into a final SQL code. We call them interpolated expressions because the result of the expression will be substituted in the source code as-is before passing it to the host language in a similar manner as template engines like Jinja2 do.
Examples:
SELECT {{ 1 + 1 }} ---> SELECT 2 SELECT {{ 2 > 5 }} ---> SELECT false
SELECT {{ "string" }} ---> SELECT string
Take into account that string values will be inserted into the final SQL code without quotes. This is done in order to be able to generate dynamic names of identifiers in SQL, like column names, table names, etc.
#+const
columnPrefix = "sales";
#+end
SELECT {{ prefix }}_amount FROM fact_sales;
Given code will generate following final SQL:
SELECT sales_amount FROM fact_sales;
If you want string values to be inserted as strings in SQL, you have to wrap your expression in quotes:
#+const
paymentType = "credit_card";
#+end
SELECT '{{ paymentType }}';
-- Following SQL will be generated:
SELECT 'credit_card';
Conditions
Conditions allows to execute arbitrary piece of code based on condition. It is defined using #+if statement. Following is a simple example where we just check in expression if debug variable is true and perform certain actions there.
#+if debug == true then SELECT ...; #+end
You can also use IF...ELSE...END constructs.
#+if debug == true then SELECT ...; #+else SELECT ....; #+end
Loops
Provides ability to loop over items in the collection.
#+for el : ["one", "two", "three"] do {{ el }} #+end
It optionally allows to specify what to put in-between (SEPARATOR) of all iterations. It is executed if there are at least two iterations. One useful scenario for it is to add comma separators for a list of column names in your SQL statement:
#+src sql Customers(fields) #+begin SELECT #+for f : fields separator "," do {{ el }} #+end FROM customers ; #+end SELECT * FROM {{ Customers(["first_name", "last_name", "email"]) }};