Code Blocks

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()
  SELECT AS store_name,
    SUM(s.quantity * s.price) AS total_sales_amount
    fact_sales AS s
  INNER JOIN dim_store AS s
    ON = s.store_id

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

But this block will not be executed until you call it via {{ selectOne() }}.

#+src sql selectOne()

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)
    country = '{{ country }}'
    AND email LIKE '%{{ domain }}'

When you call a block you can pass values to block formal parameters as positional arguments:

SELECT * FROM {{ GetCustomerByEmail("USA", "") }};

Or you can pass values as named arguments:

SELECT * FROM {{ GetCustomerByEmail(country="USA", domain="") }}

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="", 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.


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.


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",
  CREATE TABLE customers(
    name TEXT NOT NULL,
    age INTEGER

  INSERT INTO customers
  WITH tmp AS(
      generate_series AS id,
      FLOOR(RANDOM() * 5)::INTEGER + 1 AS idx,
      FLOOR(RANDOM() * ({{ maxAge }} - {{ minAge }} + 1))::INTEGER + {{ minAge }} AS age
      generate_series(1, {{ cnt }})
    (SELECT name FROM names WHERE id = idx) AS name,

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()
  SELECT 1 AS one;

SELECT * FROM {{ selectOne() }};

Will be executed on database platform as following SQL script:

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()
  DROP TABLE IF EXIST tmp_table_name;
  SELECT * FROM ...;

  -- any other transformation logic

  -- SELECT statement to produce final results

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.


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
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request