Month: August 2012

Filling the gaps with window functions

Couple of days ago I had a problem that I couldn't solve after ~ 2 hours, and decided to ask on IRC. Almost immediately after asking, I figured out the solution, but David asked me to write about the solution, even though it's now (for me) completely obvious.

The problem was like this:

I had two tables, with very simple structure: event_when timestamptz, event_count int4, and wanted to show it as a single recordset with columns: event_when, event_count_a, event_count_b, but the problem was that event_when usually didn't match. Here is an example:

Continue reading Filling the gaps with window functions

Waiting for 9.3 – Implement SQL-standard LATERAL subqueries.

On 7th of August, Tom Lane committed patch:

Implement SQL-standard LATERAL subqueries.
 
This patch implements the standard syntax of LATERAL attached to a
sub-SELECT in FROM, and also allows LATERAL attached to a function in FROM,
since set-returning function calls are expected to be one of the principal
use-cases.
 
The main change here is a rewrite of the mechanism for keeping track of
which relations are visible for column references while the FROM clause is
being scanned.  The parser "namespace" lists are no longer lists of bare
RTEs, but are lists of ParseNamespaceItem structs, which carry an RTE
pointer as well as some visibility-controlling flags.  Aside from
supporting LATERAL correctly, this lets us get rid of the ancient hacks
that required rechecking subqueries and JOIN/ON and function-in-FROM
expressions for invalid references after they were initially parsed.
Invalid column references are now always correctly detected on sight.
 
In passing, remove assorted parser error checks that are now dead code by
virtue of our having gotten rid of add_missing_from, as well as some
comments that are obsolete for the same reason.  (It was mainly
add_missing_from that caused so much fudging here in the first place.)
 
The planner support for this feature is very minimal, and will be improved
in future patches.  It works well enough for testing purposes, though.
 
catversion bump forced due to new field in RangeTblEntry.

Continue reading Waiting for 9.3 – Implement SQL-standard LATERAL subqueries.

“PostgreSQL: Up and Running” by Regina Obe and Leo Hsu

Around a week ago, I got mail saying that authors wanted me to have their book. For free. That's relatively important because getting things for free means you should pay for it in other way (that's my position, it was not suggested in any way in the mails). Plus – I generally never read technical books (really – I just don't), so there was no chance I would get it myself, if it wasn't gifted.

After some mails I got mobi version of PostgreSQL: Up and Running.

Continue reading “PostgreSQL: Up and Running" by Regina Obe and Leo Hsu