Skip to content

Home

Jump to bottom Edit New page
Sasha Aliashkevich edited this page Dec 28, 2016 · 8 revisions

SQL Tabs Documentation

Table of Contents

Running SQL

SQL Tabs can run single SQL statements as well as scripts. When you choose in menu Run Query the whole content of editor area is sent to Postgresql server as a single script. In case there were multiple statements the result area will display query result for each of them the similar way psql does that.

When you want to execute only part of script just select a needed area and press Run Query. Another way of partial execution is to split script to blocks by lines started with three dashes like this:

SELECT 1;

--- block separator

SELECT 2;

--- one more block

SELECT 3;

If you do so and press Execute Block menu item then the statements of block where cursor is placed will be run.

Info about database objects

The summary info about database and its schemas is accesible after pressing menu item Database -> Database Info. There are clickable objects so you can navigate between object without any typing.

In order to get information about particular database object you can write its name in the editor and place the cursor on it. Then pick an Object Info menu item under Database menu or press a corresponding shortcut.

In case there were no object under cursor the database summary will be displayed.

In order to list all objects of particular schema you can type the schema name with dot in the end and press Object Info shourtcut. Like myschema.

Charts

In order to visualize query result you need to prepend query with special block annotation:

--- chart <chart_type>

and press Execute Block. See examples below.

At the moment the following chart types supported:

Row charts:

  • pie
  • donut

Column charts:

  • line
  • spline
  • bar
  • area
  • step
  • area-spline
  • area-step

Row charts are the ones where first column of resultset represents a title of the value to be displayed and further columns the numeric values themselves.

Column charts take a column name as a title and rows as a values to be displayed.

Chart Examples

Donut is a row oriented chart, so each record should represent a title and a numeric value:

--- chart donut
SELECT
    relname,
    pg_total_relation_size(oid)
FROM pg_class
ORDER BY 2 DESC
LIMIT 10

By default a record number is used for axis X of column oriented charts. In order to set a column as an axis X use the following expression: chart <type> x=<N> Where N is the number of column in the resultset.

--- chart bar x=2
select random() as value,
(now() - n * interval '1 day')::date as day
from generate_series(1, 10) n

Connecting via SSH

It is possible to connect to the database via SSH. For that you need to use a special connection string syntax:

ssh://user@host:port | postgres://user@host:port/dbname

In that case connection string is devided into two parts by a pipe sign |. The first part of the connection string contains the options for connecting to the host via ssh. The second part contains options for connecting to the database as you would be connecting being on the remote host.

ssh://osuser@remote_host | postgres://dbuser@localhost/dbname

An ssh password authentication is not supported, so in order to get authenticated on remote host you need to have configured key-based aithentication. It is possible to use an alternative ssh private key rather than default id_rsa. Here is an example of connecting to a local vagrant box via ssh:

ssh://vagrant@localhost:2222/?identity_file=~/myproject/.vagrant/machines/myhost/virtualbox/private_key | postgres://postgres@localhost:5432

CSV and hidden blocks

It's possible to change the display of dataset from tabular way to CSV just by adding an annotation in front of a query:

--- csv
select * from pg_class

If you want to hide a part of output of a SQL script just create a hidden SQL block with the corresponding annotation:

show DateStyle;
select now();
--- hidden
set DateStyle = 'German'
---
show DateStyle;
select now();
--- hidden
set DateStyle = 'default';

Markdown

SQL Tabs can add to the output an arbitrary formatted content. Thus you can add any comments, titles, links and images. For these purposes in the beginning or in the end of each sql block you can add a special comments embraced into /** **/. The inside content of such comments should follow a Markdown syntax.

In order to render a markdown content execute your SQL via "Execute Block" or "Execute All Blocks" menu items or corresponding keyboard shortcuts.

Example:

/** 
## Top 10 relations 
**/
SELECT 
    relname,
    pg_total_relation_size(oid)
FROM pg_class
ORDER BY 2 DESC
LIMIT 10

/** 
For more information visit [www.sqltabs.com](http://www.sqltabs.com/)
**/

Share

In order to share the output with anybody press a "Share" button placed in the top right corner of output area. After pressing it SQL Tabs will publish your document on the publicly available web site and display the URL.

Here is an example of shared document: http://www.sqltabs.com/api/1.0/docs/7cc003a1fc830d8fdc58b4b2ac509517

Export Results to CSV and JSON

In order to export currently selected datasets into CSV or JSON files press menu item File -> Export to JSON or File -> Export to CSV

Add a custom footer
Add a custom sidebar

Clone this wiki locally