On 5th of February, Tom Lane committed patch:
Allow SQL-language functions to reference parameters by name. Matthew Draper, reviewed by Hitoshi Harada
Continue reading Waiting for 9.2 – Named parameters in SQL functions
Tag: functions
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:
Waiting for 9.1 – ALTER TYPE
On 26th of September, Peter Eisentraut committed patch which adds:
ADD ALTER TYPE ... ADD/DROP/ALTER/RENAME ATTRIBUTE LIKE WITH TABLES, this also requires allowing the existence OF composite types WITH zero attributes. reviewed BY KaiGai Kohei
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
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).
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.
Waiting for 8.4 – pl/* srf functions in selects
On 28th of October Tom Lane committed his patch that changes some internals of functions, but it also adds interesting capability.
Continue reading Waiting for 8.4 – pl/* srf functions in selects
Waiting for 8.4 – \ef in psql
Today, Tom Lane committed patch, written by Abhijit Menon-Sen, which adds interesting feature to psql. Namely – it simplifies changing definition of functions.
Waiting for 8.4 – RETURNS TABLE
Pavel Stehule wrote, and Tom Lane committed patch which ads new syntax for set-returning functions: “RETURNS TABLE".