Getting value from dynamic column in pl/PgSQL triggers?

Every so often, on irc, someone asks how to get value from column that is passed as argument.

This is generally seen as not possible, as pl/PgSQL doesn't have support for dynamic column names.

We can work around it, though. Are the workarounds usable, in terms of performance?

Continue reading Getting value from dynamic column in pl/PgSQL triggers?

Waiting for PostgreSQL 11 – Transforms for jsonb to PL/Python and to PL/Perl

On 28th of March 2018, Peter Eisentraut committed patch:

Transforms for jsonb to PL/Python
 
Add a new contrib module jsonb_plpython that provide a transform between
jsonb and PL/Python.  jsonb values are converted to appropriate Python
types such as dicts and lists, and vice versa.
 
Author: Anthony Bykov <a.bykov@postgrespro.ru>

and then, on 3rd of April 2018, he also committed patch:

Transforms for jsonb to PL/Perl
 
Add a new contrib module jsonb_plperl that provides a transform between
jsonb and PL/Perl.  jsonb values are converted to appropriate Perl types
such as arrays and hashes, and vice versa.
 
Author: Anthony Bykov <a.bykov@postgrespro.ru>

Continue reading Waiting for PostgreSQL 11 – Transforms for jsonb to PL/Python and to PL/Perl

Waiting for 9.5 – Add transforms feature

On 26th of April, Peter Eisentraut committed patch:

Add transforms feature
 
This provides a mechanism for specifying conversions between SQL data
types and procedural languages.  As examples, there are transforms
for hstore and ltree for PL/Perl and PL/Python.
 
reviews by Pavel Stěhule and Andres Freund

Continue reading Waiting for 9.5 – Add transforms feature

Variables in SQL, what, how, when?

One of the questions that gets asked quite a lot, is: how can you use variables in your queries? Other databases have them, does PostgreSQL?

Actually the answer is “no". But, it is actually very easy to work around, and what's more important – quite often, what you need are not variables in queries, but rather variables in client. What does it mean? Let's see.

Continue reading Variables in SQL, what, how, when?

Waiting for 9.1 – Arrays in PL/Perl

On 18th of February, Alvaro Herrera committed patch:

Convert Postgres arrays to Perl arrays on PL/perl input arguments
 
More generally, arrays are turned in Perl array references, and row and
composite types are turned into Perl hash references.  This is done
recursively, in a way that's natural to every Perl programmer.
 
To avoid a backwards compatibility hit, the string representation of
each structure is also available if the function requests it.
 
Authors: Alexey Klyukin and Alex Hunsaker.
Some code cleanups by me.

Continue reading Waiting for 9.1 – Arrays in PL/Perl

Test driven development for PostgreSQL

I have a mixed love/hate relationship with tests.

I hate writing them. I hate remembering to add them when I'm in the zone, and application code is flowing freely from the tips of my fingers.

But when I do add them, I absolutely love the ability to twist and replace the most core innards of application, and be able to tell that at least the sanity check of the code passes.

I love them even more when they prevent me for repeating some mistake/bug – i.e. when there is a bug, and I have tests (which clearly failed, as they didn't catch the bug), I add test for this specific bug, so I know that in future it will not happen again.

For a long time I've been fan of stored procedures (or functions) – of course not for everything, but where it makes sense.

Continue reading Test driven development for PostgreSQL

Writing sprintf, and overcoming limitations in pl/perl

Having new VARIADIC functions, I decided it would be cool to be able to write sprintf() function.

Basically the idea is simple, plperl function, which takes “format", and list of arguments and returns generated output.

Continue reading Writing sprintf, and overcoming limitations in pl/perl