February 13th, 2012 by depesz | Tags: , , , , , | No comments »
Did it help? If yes - maybe you can help me?

On 5th of February, Tom Lane committed patch:

Allow SQL-language functions to reference parameters by name.
 
Matthew Draper, reviewed by Hitoshi Harada

SQL language for writing functions has some benefits – including the fact that they are very simple, can be inlined, and are always available (pre 9.0 pl/PgSQL had to be specially installed in every database you'd wish to use it).

But their largest drawback, for me, was that they couldn't work with named parameters, so you was always faced with using names like $1, $2, $3, and so on.

Now, thanks to patch by Matthew, we finally can name the parameters:

CREATE FUNCTION to_epoch( point_in_time timestamptz ) RETURNS FLOAT8 as $$
SELECT date_part( 'epoch', point_in_time );
$$ language sql immutable;

and of course it works:

$ select to_epoch( now() );
     to_epoch
──────────────────
 1329063382.39325
(1 row)

Personally, I love it – the fact that I couldn't name my parameters was always a big no-no for me, as I tend to value readability, and using positional names is hard to understand – especially once you have functions with non-trivial parameter count.

Leave a comment