CoginitiScript was designed to encourage good software engineering practices. One of the guiding principles of high-quality software is the DRY principle - Don’t Repeat Yourself, which basically means that you should never write the same code twice. You should reuse and build upon existing code as much as possible.
“Code Blocks” are the most basic building blocks that allow code reuse. Packages (see below) are the next step into code reusability. They help you organize related CoginitiScript source files together into a single unit, making them modular, reusable, and maintainable.
Block Definition
SQL block which calculates total sales amount per each store:
#+src sql SalesPerStore() #+begin SELECT s.name AS store_name, SUM(s.quantity * s.price) AS total_sales_amount FROM fact_sales AS s INNER JOIN dim_store AS s ON s.id = s.store_id GROUP BY s.name #+end
To invoke block of code from another block, reference it via {{ block-name(args) }}
in a context where you would normally reference a database table.
SELECT * FROM {{ SalesPerStore() }} WHERE store_name = 'Main Store';
Block Execution
When you run whole script anonymous blocks executed immediately. Execution of named block happens when they are referenced using {{ block-name(args) }}
syntax.
Following block will be executed immediately.
#+src sql #+begin SELECT 1; #+end
But this block will not be executed until you call it via {{ selectOne() }}
.
#+src sql selectOne() #+begin SELECT 1; #+end SELECT * FROM {{ selectOne() }}; -- this is a line where actual execution of block happens.
When your cursor inside the block and you execute it by run at cursor the block is executed, even if it is named. It is done for better development experience, because when you are working on a certain block it is handy to run it in this way and see the result.
Block Formal Parameters
Blocks can take zero or many parameters. The following example is a basic block which takes a country and email domain parameter and returns a customers which has given email domain and live in the given country.
#+src sql GetCustomerByEmail(country, domain) #+begin SELECT * FROM dim_customers WHERE country = '{{ country }}' AND email LIKE '%{{ domain }}'
#+end
When you call a block you can pass values to block formal parameters as positional arguments:
SELECT * FROM {{ GetCustomerByEmail("USA", "gmail.com") }};
Or you can pass values as named arguments:
SELECT * FROM {{ GetCustomerByEmail(country="USA", domain="gmail.com") }}
The order of these arguments matters when they’are passed positionally but it doesn’t matter when they’re passed by their name, since it’s the name that matters, not the position:
SELECT * FROM {{ GetCustomerByEmail(domain="gmail.com", country="USA") }}
Note that you can’t mix positional and named arguments in a single block call.
Block Return Type
When you define a block you can optionally specify a type it returns. Available options: “recordset”, “void”. By default “recordset” is used.
Recordset
SQL blocks with recordset
return type execute an arbitrary list of SQL statements, returning the result of the last query in the list which must be a SELECT or WITH statement.
Void
If you want to define a SQL block that performs actions without returning any values, you can define it as returning void
. For example, this block generates a table customers
with random data:
#+src sql GenCustomersTable(cnt, minAge, maxAge): void #+meta { :doc "Generate customers table", } #+begin DROP TABLE IF EXISTS customers; CREATE TABLE customers( id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL, age INTEGER ); INSERT INTO customers WITH tmp AS( SELECT generate_series AS id, FLOOR(RANDOM() * 5)::INTEGER + 1 AS idx, FLOOR(RANDOM() * ({{ maxAge }} - {{ minAge }} + 1))::INTEGER + {{ minAge }} AS age FROM generate_series(1, {{ cnt }}) ) SELECT id, (SELECT name FROM names WHERE id = idx) AS name, age FROM tmp #+end
You can call given block using our regular syntax:
{{ GenCustomersTable(100, 18, 80) }}
What happens under the hood
When CoginitiScript execute a block which has been called from another block it creates a temporary table with results of the last SELECT statement from the block and then use the name of that temporary table in a place where block has been referenced.
For example, given CoginitiScript code:
#+src sql selectOne() #+begin SELECT 1 AS one; #+end SELECT * FROM {{ selectOne() }};
Will be executed on database platform as following SQL script:
CREATE TEMPORARY TABLE _random_name_ AS SELECT 1 AS one; SELECT * FROM _random_name_;
Please note, that your block SQL code is not limited to just single SELECT statement. It could have complex scripts with several statements, with steps where you create your own temporary tables, etc. The only requirement that you should follow if you want to reference this block is to have SELECT statement which produces result as your last statement in the block SQL.
#+src sql foo() #+begin DROP TABLE IF EXIST tmp_table_name; CREATE TEMPORARY TABLE tmp_table_name AS SELECT * FROM ...; -- any other transformation logic -- SELECT statement to produce final results SELECT * FROM ... #+end SELECT * FROM {{ foo() }} WHERE name = 'foo';In future versions CoginitiScript might not use temporary tables for all cases when block is referenced. For blocks which has single SELECT statements in it, it might just embed it as a subquery into the block from which it has been referenced.
Limitations
In the current version of CoginitiScript it has following limitations:
- On Snowflake platform is it required to have explicit column aliases specified on SELECT statement in block which is referenced from other places due to limitation of the platform itself which is not allow unnamed columns in queries when creating temporary tables.
- Explain plan cannot be executed for statement with block reference