Passing Parameters to Queries & Controls

A query may optionally define parameters. When executing the query, you can specify values for the parameters to be used for the query execution.

Using Parameters in Queries

Variable usage and interpolation in Powerpipe is based on and conforms to Terraform. Special consideration must be made for passing variables into queries, however, as both the HCL parser AND the SQL parser must account for the variables.

When defining a query, you may use positional parameters ($1, $2, $3, ...) in the query definition. For each of these positional parameters, you should define a param block that names and describes the parameter. Note that Powerpipe will assign the parameters in the order that the param blocks are defined - the first param block describes $1, the second describes $2, etc:

You can also pass list values as parameters, and they will converted to PostgreSQL arrays in the query:

Passing Arguments

You can run a query or control by name from the command line. If the query provides defaults for all the parameters, you can run it without arguments in the same way you would run a query or control that takes no parameters, and it will run with the default values:

If the query does not provide a default, or you wish to run the query with a different value, you can pass an argument to the query with one or more --arg arguments.

You can pass them by name:

Or by position. If no argument name is provided, the arguments will be passed to the query in the order they are passed to the command (the first --arg as $1, the second as $2, etc.):

Likewise, when specifying arguments in HCL, you can pass them by name (as a map):

Or by position (as a list):

Using Parameters in Controls, Charts, and other resources

Controls, charts, cards, and many other resources allow you to refer to a parameterized query with the query argument, and you can pass arguments to the query in the args argument:

Alternatively, you may specify inline sql, and define param blocks as you would for a query:

Note that you may either reference a query object with the query argument or use inline SQL with the sql argument from your control, but not both, and the behavior is subtly different, as can be seen in the examples above:

• The query argument is a reference to a query resource. You cannot define parameters (param blocks) for the control, but you can pass them as arguments (args) to the query if the query has parameters defined.
• The sql argument is a string. When the control specifies a SQL string, it essentially behaves like a query, and thus you can define the parameters that it accepts (in param blocks) in the same manner as a query resource.

Using Parameters with Variables

It is common for arguments and parameter defaults to refer to input variables, so that users of the mod can change the values without modifying the code:

Using Parameters with Inputs

It is common for arguments to refer to dashboard input elements, allowing you to create rich, dynamic, interactive reports: