To make it up
to Simon, we shall demonstrate 2 features we left out in our prior article.
Pretty SVG graphs -- so I don't have to hear more about SVG from Simon and how I'm caught in the 70s.
Ability to plot a schedule -- schedule item, start date, end date using a multi-column aggregate function. Here is a good use for multi-column aggregates or at least I think so. (Yeh for Multi-column aggregates)
In this article we shall demonstrate how to create multi-column aggregates with PL/Python. Note you can do this with any PL or SQL language in PostgreSQL,
but Python happens to have a lot of nifty libraries we wanted to put to use. For this exercise,
create a function agg_svgschedule. What it will do is plot each grouping of data and return an svg schedule plot for each grouping. This aggregate will take 3 columns - a schedule item, a start date and an end date.
we covered in How to create multi-column aggregates, can be applied here.
Since PL/Python does not support arrays as input, our aggregator will form a pipe, | delimeted list inputs
as a string using svgplot_scheduledatabuild function (which although we wrote it in python need not be and probably more efficient to write in plpgsql). It must be noted that this particular library takes a single paired array of data points. So the number of elements must be in multiples
so we need only one delimiter as we had in our ascii plot example.
As the final step of the aggregation, this is fed to our svgplot_scheduledata function -- which converts this to an array of strings to be fed to the plotter
The other nice thing about this library is that it doesn't use print, so we don't need to redirect the stdout as we had to with the ascii plot
Note this SVG library contains other things such as pie charts, regular graphs, bar charts, but we thought the schedule one looked kind of nifty and made the most sense for aggregate svg plots
Installing Py-SVG is pretty easy. At the command-line run the following: