On 17th of July, Noah Misch committed patch:
Implement the FILTER clause for aggregate function calls. This is SQL-standard with a few extensions, namely support for subqueries and outer references in clause expressions. catversion bump due to change in Aggref and WindowFunc. David Fetter, reviewed by Dean Rasheed.
Continue reading Waiting for 9.4 – Implement the FILTER clause for aggregate function calls.
In previous post in the series I wrote about how to interpret single line in explain analyze output, it's structure, and later on described all basic data-getting operations (nodes in explain tree).
Today, we'll move towards more complicated operations.
On 10th of March, Andrew Dunstan committed patch:
JSON generation improvements.
This adds the following:
json_agg(anyrecord) -> json
to_json(any) -> json
hstore_to_json(hstore) -> json (also used as a cast)
hstore_to_json_loose(hstore) -> json
The last provides heuristic treatment of numbers and booleans.
Also, in json generation, if any non-builtin type has a cast to json,
that function is used instead of the type's output function.
Andrew Dunstan, reviewed by Steve Singer.
Catalog version bumped.Continue reading Waiting for 9.3 – JSON generation improvements.
And maybe not on the wall, but instead in your SQLz, eating your data.
But a bit more seriously. Ever since PostgreSQL 8.4 we have window functions, but still I see people which do not know it or are wary to use it.
That's why I decided to write a piece on window functions. How they work and what they can be used for.
Let's assume you have some simple database with “articles" – each article can be in many “categories". And now you want to get list of all articles in given set of categories.
Standard approach:
SELECT a.* FROM articles AS a JOIN articles_in_categories AS aic ON a.id = aic.article_id WHERE aic.category_id IN (14,62,70,53,138)
Will return duplicated article data if given article is in more than one from listed categories. How to remove redundant rows?
On 1st of Februyary, Takahiro Itagaki committed a patch by Pavel Stehule which adds string_agg aggregate:
Log Message: ----------- Add string_agg aggregate functions. The one argument version concatenates the input values into a string. The two argument version also does the same thing, but inserts delimiters between elements. Original patch by Pavel Stehule, reviewed by David E. Wheeler and me.
On 15th of December Tom Lane committed patch by Andrew Gierth (aka RhodiumToad), which adds interesting capability:
Log Message: ----------- Support ORDER BY within aggregate function calls, at long last providing a non-kluge method for controlling the order in which values are fed to an aggregate function. At the same time eliminate the old implementation restriction that DISTINCT was only supported for single-argument aggregates. Possibly release-notable behavioral change: formerly, agg(DISTINCT x) dropped null values of x unconditionally. Now, it does so only if the agg transition function is strict; otherwise nulls are treated as DISTINCT normally would, ie, you get one copy. Andrew Gierth, reviewed by Hitoshi Harada
Today, on irc (#postgresql on freenode.net) Dim mentioned about writing median calculation code.
It got me thinking, and consequently writing my version of median calculation code.
A long overdue post about new functionality. At this moment it is no longer such new, as it was committed on 28th of December (yes, I know, I should have written it earlier, Sorry).
On this day Tom Lane committed patch by Hitoshi Harada which adds support for so called window functions:
Support window functions a la SQL:2008. Hitoshi Harada, with some kibitzing from Heikki and Tom.
Finally, we got very important addons to PostgreSQL, which help with dealing with arrays.
It solves a lot of problems, which were usually solved with standard cookbook code, which was in faqs, blog posts and number of examples on irc.
Continue reading Waiting for 8.4 – array aggregate and array unpacker