Tag: array_agg

Waiting for 9.5 – Support arrays as input to array_agg() and ARRAY(SELECT …).

On 25th of November, Tom Lane committed patch:

Support arrays as input to array_agg() and ARRAY(SELECT ...).
 
These cases formerly failed with errors about "could not find array type
for data type".  Now they yield arrays of the same element type and one
higher dimension.
 
The implementation involves creating functions with API similar to the
existing accumArrayResult() family.  I (tgl) also extended the base family
by adding an initArrayResult() function, which allows callers to avoid
special-casing the zero-inputs case if they just want an empty array as
result.  (Not all do, so the previous calling convention remains valid.)
This allowed simplifying some existing code in xml.c and plperl.c.
 
Ali Akbar, reviewed by Pavel Stehule, significantly modified by me

Continue reading Waiting for 9.5 – Support arrays as input to array_agg() and ARRAY(SELECT …).

Window, window on the wall …

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.

Continue reading Window, window on the wall …

Waiting for 8.5 – ordered aggregates

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

Continue reading Waiting for 8.5 – ordered aggregates

Waiting for 8.4 – array aggregate and array unpacker

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