By adding special comments to a SQL (select) statement, you can influence the way the result is displayed in SQL Workbench/J. This comments are called "annotations" and must be included in a comment preceding the statement that is executed. The comment can be a single line or multi-line SQL comment
You can change the name of the result tab associated with a statement. To give a result
set a name, use the annotation
@WbResult followed by the name that should
appear as the result's name.
The following examples executes two statements. The result for the first will be labelled "List of contacts" and the second will be labelled "List of companies":
-- @WbResult List of contacts SELECT * FROM person; /* @WbResult List of companies this will retrieve all companies from the database */ SELECT * FROM company;
The result name that is used, will be everything after the annotation's keyword until the end of the line.
For the second select (with the multi-line comment), the name of the result tab will be
List of companies, the comment on the second line will not be considered.
If the result of a query should be displayed in an existing result tab, the annotation
together with a tab name can be used. If this annotation is present and a result tab with that name already
exists, the existing result will be replaced with the new result. If no result tab with that name exists,
a new tab (with the supplied name) will be created.
Re-using a result tab only works if → is enabled.
You can combine |
If the following query is run for the second time, the existing data will be replaced with the newly retrieved data:
-- @WbUseTab List of contacts SELECT * FROM person;
@WbScrollTo can be used to automatically scroll a result set after it has been retrieved
to a specific row number. The row number has to be supplied using a
-- @WbScrollTo #100 SELECT * FROM person;
In addition to a row number, the special values
last (without a
are also recognized. When they are supplied, the result is automatically scrolled to the last row.
This is useful when displaying the contents of log tables.
-- @WbScrollTo end SELECT * FROM activity_log;
@WbAppendResult can be used to always append the result of the associated query regardless of
the current setting of → .
To suppress an empty result, the annotation
@WbRemoveEmpty can be used. If a query
returns no rows and contains this annotation, no result tab will be created. No warning or message will
be shown if this happens!
To automatically refresh a result in a defined interval, the
can be used. The interval is specified as a parameter to the annotation:
-- @WbRefresh 15s SELECT * FROM pg_stat_activity;
The automatic refresh can also be enabled through the context menu of the result tab.
If the automatic optimization of the row height is disabled, the annotation
can be used to optimize the height of the rows for a specific SQL query.
The annotation supports the parameter
lines that defines the maximum number of lines
to display, even if the content was longer.
-- @WbOptimizeRowHeight lines=10 select id, json_payload from messages order by id;
@WbKeepResult will "lock" a result tab, i.e. the result will never be closed
automatically. This can also be enabled through the context menu of a result.
@WbCrossTabl can automatically transpose rows to columns from a result of a query.
This is also known as "crosstab" or "pivot".
Assuming the following statement
select department_id as dept, count(*) as "Employees", avg(salary)::int as "Avg. Salary", min(salary)::int as "Min. Salary" from employees where department_id in (10,20,30) group by department_id order by department_id;
Returns the following result:
dept | Employees | Avg. Salary | Min. Salary -----+-----------+-------------+------------ 10 | 1 | 4400 | 4400 20 | 2 | 9500 | 6000 30 | 6 | 4150 | 2500
Then the result can be turned into a crosstab by adding the annotation
The annotation supports two parameters:
-- @WbCrossTab labelColumn=dept addLabel="Dept " select department_id as dept, count(*) as "Employees", avg(salary) as "Avg. Salary", min(salary) as "Min. Salary" from employees where department_id in (10,20,30) group by department_id order by department_id;
Returns the following result:
| Dept 10 | Dept 20 | Dept 30 ------------+---------+---------+-------- Employees | 1 | 2 | 6 Avg. Salary | 4400 | 9500 | 4150 Min. Salary | 4400 | 6000 | 2500
If this is used to export a crosstab using
the data is always read completely into memory in order to be able to
generate the crosstab - even if the query would normally be streamed directly to the export file.