So, some time ago, Pg devs added multi ranges – that is datatype that can be used to store multiple ranges in single column.
The thing is that it wasn't really simple how to get list of ranges from within such multirange. There was no operator, no way to split it.
A month ago Alexander Korotkov committed patch that added unnest() over multiranges, but it got some problems, and was reverted
It will eventually made it's way into sources, I assume, but in the mean time – a friend of mine asked how to get list of elements from multiranges. So I looked into it.
Continue reading How to get list of elements from multiranges?
On 11th of March, Teodor Sigaev committed patch:
Tsvector editing functions
Adds several tsvector editting function: convert tsvector to/from text array,
set weight for given lexemes, delete lexeme(s), unnest, filter lexemes
with given weights
Author: Stas Kelvich with some editorization by me
Reviewers: Tomas Vondram, Teodor Sigaev
Continue reading Waiting for 9.6 – Tsvector editing functions
On 22nd of November, Tom Lane committed patch:
Support multi-argument UNNEST(), and TABLE() syntax for multiple functions.
This patch adds the ability to write TABLE( function1(), function2(), ...)
as a single FROM-clause entry. The result is the concatenation of the
first row from each function, followed by the second row from each
function, etc; with NULLs inserted if any function produces fewer rows than
others. This is believed to be a much more useful behavior than what
Postgres currently does with multiple SRFs in a SELECT list.
This syntax also provides a reasonable way to combine use of column
definition lists with WITH ORDINALITY: put the column definition list
inside TABLE(), where it's clear that it doesn't control the ordinality
column as well.
Also implement SQL-compliant multiple-argument UNNEST(), by turning
UNNEST(a,b,c) into TABLE(unnest(a), unnest(b), unnest(c)).
The SQL standard specifies TABLE() with only a single function, not
multiple functions, and it seems to require an implicit UNNEST() which is
not what this patch does. There may be something wrong with that reading
of the spec, though, because if it's right then the spec's TABLE() is just
a pointless alternative spelling of UNNEST(). After further review of
that, we might choose to adopt a different syntax for what this patch does,
but in any case this functionality seems clearly worthwhile.
Andrew Gierth, reviewed by Zoltán Böszörményi and Heikki Linnakangas, and
significantly revised by me
Continue reading Waiting for 9.4 – Support multi-argument UNNEST(), and TABLE() syntax for multiple functions.
On 11th of July, Tom Lane committed patch:
Add array_remove() and array_replace() functions. <span class="refs"> <span class="head" title="heads/master"><a href="/gitweb/?p=postgresql.git;a=shortlog;h=refs/heads/master">master
These functions support removing or replacing array element value(s)
matching a given search value. Although intended mainly to support a
future array-foreign-key feature, they seem useful in their own right.
Marco Nenciarini and Gabriele Bartolini, reviewed by Alex Hunsaker
Continue reading Waiting for 9.3 – Add array_remove() and array_replace() functions.
On 16th of February, Tom Lane committed patch:
Add FOREACH IN ARRAY looping to plpgsql.
(I'm not entirely sure that we've finished bikeshedding the syntax details,
but the functionality seems OK.)
Pavel Stehule, reviewed by Stephen Frost and Tom Lane
Continue reading Waiting for 9.1 – FOREACH IN ARRAY
Let's imagine simple situation – you have table of objects (each with id), and you want objects 3, 71, 5 and 16. And in that order!
How to do it?
Continue reading How to order by some random – query defined – values?
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