json functions for dynamic field modification in plpgsql, including in triggers.

One of the occasionally annoying things about Plpgsql is that it's very static, and any sort of dynamic access to records, say by using a trigger argument, is difficult. I just conducted a little experiment that seems to make life a bit easier. The json_populate_record() function returns a record of the same type as its first argument. Mostly we just use NULL::record_type here, but it turns out that in a trigger function it works perfectly happily with NEW and OLD, and they can be modified using any sort of dynamic values you like, including values from TG_ARGV

Here's a simple example:

mydb=# create type rainbow as enum('r','o','y','g','b','i','v');
CREATE TYPE
mydb=# create table foo (a text, b int, c boolean, d rainbow);
CREATE TABLE
mydb=# create or replace function foo_tr()
returns trigger
language plpgsql as $$
begin
  if tg_op = 'INSERT' or tg_op = 'UPDATE'
  then
    return json_populate_record(NEW,json_object(TG_ARGV));
  else
    return old;
  end if;
end;
$$;
CREATE FUNCTION
mydb=# create trigger foo_tri before insert on foo for each row execute procedure foo_tr(d,i);
CREATE TRIGGER
mydb=# create trigger foo_tru before update on foo for each row execute procedure foo_tr(c,true);
CREATE TRIGGER
mydb=# insert into foo values ('x',1,null,'r') returning *;
 a | b | c | d 
---+---+---+---
 x | 1 |   | i
(1 row)

INSERT 0 1
mydb=# update foo set c = null, d = 'o' returning *;
 a | b | c | d 
---+---+---+---
 x | 1 | t | o
(1 row)

UPDATE 1
mydb=#

Numeric scale and precision

From the quick tip file:

I have lost track of the number of times I have seen people get this wrong. The precision of numeric doesn't specify the number of digits allowed before the decimal point, it specifies the total number of digits before and after the decimal point. So the number allowed before the decimal point is the precision minus the scale. And that means that if you want to increase the scale for finer grained numbers without reducing the range of numbers allowed you need to increase both the precision and the scale.

Let's say you want to have money amounts of US dollars with no fractional cents and up to single trillions of dollars. You would use numeric(15,2). That allows thirteen digits before the decimal and two after. Now if you want to change that to allow fractional cents in hundredths, you will need to use numeric(17,4), which still allows thirteen digits before the decimal but now allows four after.

psql -c will no longer imply --no-psqlrc

Recently some very good changes were made that allow you to have multiple -c and -f arguments to psql. This is going to be quite useful. One of the things this changed as a part of this is that -c no longer implies --no-psqlrc. I found this out the hard way when it proved to be that cause of unpleasant failures by one of my buildfarm animals. This animal runs on my normal Unix workstation, where I have a .psqlrc doing various things, and it also runs a non-standard module that calls "psql -c". Until now there was no issue, because -c meant that my .psqlrc was not processed. The solution was to as "--no-psqlrc" to those psql calls. But I suspect this might catch quite a few people once 9.6 is released.