with
Some resources may also include with blocks. Similar to a with clause in a Postgres CTE, the with block allows you to specify additional queries or SQL statements to run first, and then pass the query results as arguments to sql, query, and node & edge blocks.
with is not a top-level named resource in its own right - it is ONLY a block within other resources.
You can only specify with blocks on dashboard, graph, hierarchy, and flow, and only when they are defined as top-level named resources in your mod. The results of the with query can be referenced only within the resource in which it is defined (including any sub-blocks).
Example Usage
Argument Reference
| Argument | Type | Optional? | Description |
|---|---|---|---|
| args | Map | Optional | A map of arguments to pass to the query. |
| database | String | Optional | A database connection reference, connection string, or Pipes workspace to query. If not specified, the default database will be used. |
| query | Query Reference | Optional | A reference to a query resource that defines the query to run. You must either specify the query argument or the sql argument, but not both. |
| --search-path | String | Optional | Set a comma-separated list of connections to use as a custom search path for the query |
| --search-path-prefix | String | Optional | Set a comma-separated list of connections to use as a prefix to the current search path for the query. |
| sql | String | Optional | An SQL string to provide data for the edge. You must either specify the query argument or the sql argument, but not both. |
Referencing with results
with blocks are scoped to the top-level resource in which they are defined. You can reference the results of a with block in any sub-block of that resource.
with blocks can only be added to top-level named resources. You cannot add with blocks to top-level anonymous resources or sub-resources.
You can reference the with query results as with.<name>.rows.
For example, given the following with block:
with.<name>.rows is a list, and you can index it to get a single row. Each row, in turn, contains all the columns, so you can get a single column of a single row:
If you splat the row, then you can get an array of a single column from all rows. This would be passed to SQL as an array:
- if a is a scalar value, then with.stuff1.rows[*].a is an array of scalar values
- if a is an array or JSONB array, then with.stuff1.rows[*].a is an array of arrays.
At this time, you cannot pass an entire set (with.<name>.rows) to a query - you may pass either a single value (with.<name>.rows[0].column) or an array from a single column (with.<name>.rows[*].column).