Grouping data into time ranges

Today some guy on IRC asked question, which I didn't fully understand, but which could (probably) be summarized: how to group data into 5 minute intervals, based on some timestamp column.

Well, it seems trivial (as long as you know how to do it), but since he clearly didn't know how to do it (or I misunderstood his problem), here's the explanation:

Continue reading Grouping data into time ranges

Waiting for 8.5 – Named function arguments

Pavel Stehule – hero for everybody writing stored procedures, wrote, and later Tom Lane committed patch which adds named arguments for functions:

Log Message:
-----------
Support use of function argument names to identify which actual arguments
match which function parameters.  The syntax uses AS, for example
funcname(value AS arg1, anothervalue AS arg2)
 
Pavel Stehule

Continue reading Waiting for 8.5 – Named function arguments

Waiting for 8.4 – Default values for function arguments + integer in any base

On 4th of December Peter Eisentraut committed patch by Pavel Stehule (with Peters tweaks) which adds default values for function arguments:

Default values for function arguments
 
Pavel Stehule, with some tweaks by Peter Eisentraut

Continue reading Waiting for 8.4 – Default values for function arguments + integer in any base

Getting list of most common domains

Today, on #postgresql on IRC, guy (can't contact him now to get his permission to name him), said:

I have a table called problematic_hostnames. It contains a list of banned hostnames in column “hostname" (varchar). I would like to display the top 10 troll ISPs based on this. Does PG have a way of spotting a “pattern"? Some ISPs are example.net while others are foo.bar.example.net, so you can't just regexp the last X.Y (since that would cause “.co.uk" to be one of the top troll ISPs).

Continue reading Getting list of most common domains

Waiting for 8.4 – sql-wrappable RETURNING

In PostgreSQL 8.2, we got “RETURNING" clause in INSERT/UPDATE/DELETE queries.

Unfortunately it could not be used as source of rows for anything in sql.

INSERT INTO table_backup DELETE FROM TABLE WHERE ... returning *;

Well, it's still not possible, but it is a one step closer, thanks to patch written and committed by Tom Lane on 31st of October:

Allow SQL-LANGUAGE functions TO RETURN the output OF an INSERT/UPDATE/DELETE
RETURNING clause, NOT just a SELECT AS formerly.
 
A side effect OF this patch IS that WHEN a set-returning SQL FUNCTION IS used
IN a FROM clause, performance IS improved because the output IS collected INTO
a tuplestore WITHIN the FUNCTION, rather than USING the less efficient
value-per-CALL mechanism.

Continue reading Waiting for 8.4 – sql-wrappable RETURNING