You're browsing the documentation of a version that is still under development, the features described here may change or even be removed.

8.2.5. Testing query with parameters

The query variables plugin helps the developers test their queries using variables that are replaced in the command at the moment of its execution. Basically, a query that contains some variables prefixed by $ like this SELECT $cols FROM $schema.$table WHERE $condition has the values for each variable replaced and the parsed query executed. The variables and their values can be specified in a special widget that is toggled by the button Variables in the SQL execution widget. The main goal of this new feature is to accelerate the query testing mainly if you write parametrized queries based on some ORMs syntaxes. This plugin supports four variable formats: $variable, :variable, @variable, and {variable}.

The button Copy parsed copies to the clipboard the resulting query after parsing the original one replacing the variables by their values. Now, theExtract button performs a scan in the original query looking for variable patterns, and, once found, puts each result in the variables grid so the user can specify test values for them. The button Options holds a dropdown menu containing two options. The first one, Empty values translate to NULL, causes empty-valued variables to be replaced by the keyword NULL. The second option, Escape quoted values with E'', forces the use of the special string quoting syntax that allows the use of C-Style constants within strings.

In the variables grid, we have the column Variable, which is the variable's name. Note that despite the variables in the original query being suffixed by a special character (e.g. $, @, :) there's no need to include that suffix in the variable name. During the query parsing, pgModeler will automatically prepend the accepted suffixes in the variable names and replace them accordingly. Also, duplicated variable names in the grid are accepted but only the first value will be considered during the parsing. The column Value holds the variable's value that will be replaced in the original query. Beware that this field is completely free of any validation, the user is responsible for any character formatting and/or escaping. Data type values or even SQL keywords are accepted in this column. Finally, the column Quoted, is a convenience feature that automatically encloses the value of the respective column in quotes (this is affected by the option Escape quoted values with E'').


Nov 1, 2024 at 10:40