pgVis Reference

pgVis implements a simple DSL for defining visualisations using a set of PostgreSQL functions.

Define Visualisation 

vis.visualise( summary TEXT, parts VARIADIC JSONB[] ) -> vis.t_definition

Start defining a pgVis visualisation definition.

The general form for defining a pgVis visualisation is: 

WITH my_query AS (
    ...
), my_other_query AS (
    ...
)
SELECT vis.visualise(
    -- Visualisation title
    'My Visualisation',
    -- specify any datasets, tiles and options
    ...,
    ...,
    ...
);

Option Functions

Simple Options 

vis.option( key TEXT, value TEXT ) -> vis.t_options
vis.option( key TEXT, value NUMERIC ) -> vis.t_options
vis.option( key TEXT, value BOOLEAN ) -> vis.t_options

Define a simple key value pair option.

Structured Options 

vis.option( key TEXT, value VARIADIC JSONB[] ) -> vis.t_options

Define a complex structured option which is built from other option key value pairs.

eg: 

SELECT vis.option('x-axis', 
  vis.option('title', 'Test'),
  vis.option('startAtZero', false)
);

Dataset Functions

Define A Shared Dataset 

vis.dataset( id TEXT, summary TEXT, data JSONB, labels JSONB DEFAULT NULL ) -> vis.t_dataset

Define a shared dataset referenced by id , with the given title, based on the given data serialised as a JSONB array.

Use A Shared Dataset 

vis.dataset( id TEXT ) -> vis.t_dataset

Use the shared dataset referenced by id .

Define A Tile Dataset 

vis.dataset( data JSONB, labels JSONB DEFAULT NULL ) -> vis.t_dataset

Define a single use dataset from a simple JSONB serialised data array for use within a single tile.

Data Chart Series Functions

Define A Chart Data Series 

series( summary TEXT, x TEXT, y TEXT, options JSONB DEFAULT '{}' ) -> vis.t_series

Add a data series to a chart with the display title summary , using x as the name of the value for the X (or labels) axis and y as the name of the value for the Y axis.

Data Table Columns Functions

Define A Data Table Column 

vis.column( summary TEXT, colName TEXT, options JSONB DEFAULT '{}' ) -> vis.t_column

Add a column to a data table with the display title summary and using colName as the name of the value to display.

Tile Functions

Data Table 

vis.data_table( id TEXT, size INTEGER, summary TEXT, parts VARIADIC JSONB[] ) -> vis.t_tile
vis.data_table( summary TEXT,parts VARIADIC JSONB[] ) -> v.t_tile

Add a data table tile to the visualisation, where:

  • id is the tile reference
  • size is an integer between 1 and 12 denoting the number of columns the tile consumes
  • summary is the tile title
  • parts are data table column, dataset or options definitions

eg: 

vis.data_table('Daily Sales',
    vis.dataset('daily_sales'),
    vis.column('Day', 'day'),
    vis.column('UK Sales', 'uk_sales'),
    vis.column('US Sales', 'us_sales')
),

Bar Chart 

vis.bar_chart( id TEXT, size INTEGER, summary TEXT, parts VARIADIC JSONB[] ) -> vis.t_tile
vis.bar_chart( summary TEXT, parts VARIADIC JSONB[] ) -> vis.t_tile

Add a bar chart tile to the visualisation, where:

  • id is the tile reference
  • size is an integer between 1 and 12 denoting the number of columns the tile consumes
  • summary is the tile title
  • parts are chart data series, dataset or options definitions

eg: 

vis.bar_chart(null, 6, 'Daily Sales',
    vis.dataset('daily_sales'),
    vis.series('UK Sales', 'day', 'uk_sales'),
    vis.series('US Sales', 'day', 'us_sales')
),

Line Chart 

vis.line_chart( id TEXT, size INTEGER, summary TEXT, parts VARIADIC JSONB[] ) -> vis.t_tile
vis.line_chart( summary TEXT, parts VARIADIC JSONB[] ) -> vis.t_tile

Add a line chart tile to the visualisation, where:

  • id is the tile reference
  • size is an integer between 1 and 12 denoting the number of columns the tile consumes
  • summary is the tile title
  • parts are chart data series, dataset or options definitions

eg: 

vis.bar_chart('line1', 4, 'Daily Sales',
    vis.dataset('daily_sales'),
    vis.series('UK Sales', 'day', 'uk_sales'),
    vis.series('US Sales', 'day', 'us_sales')
),

Scatter Chart 

vis.scatter_chart( id TEXT, size INTEGER, summary TEXT, parts VARIADIC JSONB[] ) -> vis.t_tile
vis.scatter_chart( summary TEXT, parts VARIADIC JSONB[] ) -> vis.t_tile

Add a scatter chart tile to the visualisation, where:

  • id is the tile reference
  • size is an integer between 1 and 12 denoting the number of columns the tile consumes
  • summary is the tile title
  • parts are chart data series, dataset or options definitions

eg: 

vis.scatter_chart('scatter1', 8, 'Daily Sales',
    vis.dataset('daily_sales'),
    vis.series('UK Sales vs US Sales', 'uk_sales', 'us_sales')
),

Pie Chart 

vis.pie_chart( id TEXT, size INTEGER, summary TEXT, parts VARIADIC JSONB[] ) -> vis.t_tile
vis.pie_chart( summary TEXT, parts VARIADIC JSONB[] ) -> vis.t_tile

Add a pie chart tile to the visualisation, where:

  • id is the tile reference
  • size is an integer between 1 and 12 denoting the number of columns the tile consumes
  • summary is the tile title
  • parts are chart data series, dataset or options definitions

eg: 

vis.pie_chart('Daily Sales',
    vis.dataset('daily_sales'),
    vis.series('UK Sales', 'day', 'uk_sales')
),

Polar Area Chart 

vis.polar_area_chart( id TEXT, size INTEGER, summary TEXT, parts VARIADIC JSONB[] ) -> vis.t_tile
vis.polar_area_chart( summary TEXT, parts VARIADIC JSONB[] ) -> vis.t_tile

Add a polar area chart tile to the visualisation, where:

  • id is the tile reference
  • size is an integer between 1 and 12 denoting the number of columns the tile consumes
  • summary is the tile title
  • parts are chart data series, dataset or options definitions

eg: 

vis.polar_area_chart('Daily Sales',
    vis.dataset('daily_sales'),
    vis.series('UK Sales', 'day', 'uk_sales'),
    vis.series('US Sales', 'day', 'us_sales')
),

Radar Chart 

vis.radar_chart( id TEXT, size INTEGER, summary TEXT, parts VARIADIC JSONB[] ) -> vis.t_tile
vis.radar_chart( summary TEXT, parts VARIADIC JSONB[] ) -> vis.t_tile

Add a radar chart tile to the visualisation, where:

  • id is the tile reference
  • size is an integer between 1 and 12 denoting the number of columns the tile consumes
  • summary is the tile title
  • parts are chart data series, dataset or options definitions

eg: 

vis.radar_chart('Daily Sales',
    vis.dataset('daily_sales'),
    vis.series('UK Sales', 'day', 'uk_sales')
),

Output Functions

Output As HTML 

vis.html( definition vis.t_definition ) -> TEXT

Wrap the pgVis visualisation in a simple HTML wrapper page, ready to be viewed in a browser.

Stored Visualisations Functions

List Stored Visualisations 

vis.list_stored_visualisations() -> JSONB

Get information about all stored visualisations which can be found as a JSON array.

Alternatively you can use the view: vis.stored_visualisations .

Run A Stored Visualisation 

vis.run_stored_visualisation( id TEXT, args JSONB DEFAULT NULL ) -> vis.t_definition

Run a stored visualisation by it's id ( schema . name ) and provide optional arguments to be passed to the stored visualisation.