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?
Previously I tested performance of pl/PgSQL coded foreign keys to partitioned table.
Now, let's see if I can make creation of them a bit easier.
Previously I wrote about how to create foreign key pointing to partitioned table.
Final solution in there required four separate functions and four triggers for each key between two tables.
Let's see how fast it is, and if it's possible to make it simpler.
One of the long standing limitations of partitions is that you can't have foreign keys pointing to them.
Let's see if I can make it possible to have some kind of constraint that would do the same thing as fkey.
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.
On 19t of January, Heikki Linnakangas committed patch:
Make pg_relation_size() and friends return NULL if the object doesn't exist. That avoids errors when the functions are used in queries like "SELECT pg_relation_size(oid) FROM pg_class", and a table is dropped concurrently. Phil Sorber
Continue reading Waiting for 9.2 – NULLS from pg_*_size() functions
On 18th of July, Tom Lane committed patch:
Add GET STACKED DIAGNOSTICS plpgsql command to retrieve exception info. This is more SQL-spec-compliant, more easily extensible, and better performing than the old method of inventing special variables. Pavel Stehule, reviewed by Shigeru Hanada and David Wheeler
Continue reading Waiting for 9.2 – Stacked Diagnostics in PL/pgSQL
On 16th of February, Tom Lane committed patch:
Add FOREACH IN ARRAY looping to plpgsql. (I'm not entirely sure that we've finished bikeshedding the syntax details, but the functionality seems OK.) Pavel Stehule, reviewed by Stephen Frost and Tom Lane
Every now and then somebody asks how to make diff of database schemata.
Usual background is like: we have production database, and development database, and we want to see what is different on development to be able to change production in the same way.
Personally I think that such approach is inherently flawed. Why?
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.