Dynamic updates of fields in NEW in PL/pgSQL

Today, on #postgresql on IRC, strk asked about updating fields in NEW record, in plpgsql, but where name of the field is in variable.

After some time, he sent his question to hackers mailing list. And he got prompt reply that it's not possible.

Well, I dare to disagree.

First, big disclaimer:

THIS IS JUST A PROOF OF CONCEPT.

DO NOT USE IN ANY REAL-LIFE APPLICATION!

No, seriously. Don't. It will eat your data, crash your server, and kill your dog.

So, now let's see what we can do. Of course the code will not be nice, but who cares.

First. Let's play with getting single field from record. This is actually pretty simple:

CREATE OR REPLACE FUNCTION get_dynamic_field_from_record(p_record ANYELEMENT, p_class TEXT, p_field TEXT) RETURNS TEXT AS $$
DECLARE
    use_sql TEXT;
    reply TEXT;
BEGIN
    use_sql := 'SELECT ($1::[CLASS]).[FIELD]';
    use_sql := REPLACE( use_sql, '[CLASS]', quote_ident(p_class) );
    use_sql := REPLACE( use_sql, '[FIELD]', quote_ident(p_field) );
    EXECUTE use_sql INTO reply USING p_record;
    RETURN reply;
END;
$$ LANGUAGE plpgsql;

Now, what does it mean? First of all – you can't get “RECORD" variables, so I had to use ANYELEMENT. It will most likely fail if you will not pass record, but that's not my problem – as I said – it's just proof of concept.

Next – we need to know which class (table) this record represents. And third – we need to know which field to extract.

Let's test it. We'll use pg_class table:

SELECT
    c.relkind,
    c.relname,
    get_dynamic_field_from_record(c, 'pg_class', 'relkind'),
    get_dynamic_field_from_record(c, 'pg_class', 'relname')
FROM
    pg_class c
LIMIT 5;
 relkind |       relname        | get_dynamic_field_from_record | get_dynamic_field_from_record 
---------+----------------------+-------------------------------+-------------------------------
 r       | pg_type              | r                             | pg_type
 r       | pg_attribute         | r                             | pg_attribute
 v       | user_mapping_options | v                             | user_mapping_options
 v       | user_mappings        | v                             | user_mappings
 v       | TABLES               | v                             | TABLES
(5 ROWS)

Not really exciting, but at the very least – we actually can get value from record.

Now, more tricky part – setting.

First of all – function that will set value in record cannot return actual record! Instead it has to return text, which will be textual representation of this record.

Why is it necessary – it's simple – single function cannot return different types of values – i.e. return value has to be defined as static. But text will work.

So, our setting function looks like this:

CREATE OR REPLACE FUNCTION set_dynamic_field_in_record(p_record ANYELEMENT, p_class TEXT, p_field TEXT, p_new_value TEXT) RETURNS TEXT AS $$
DECLARE
    use_sql TEXT := NULL;
    temprec RECORD;
    use_val TEXT;
    reply   TEXT;
BEGIN
    FOR temprec IN SELECT a.attname FROM pg_attribute a JOIN pg_class c ON a.attrelid = c.oid WHERE c.relname = p_class AND NOT a.attisdropped AND a.attnum > 0 ORDER BY a.attnum LOOP
        IF temprec.attname = p_field THEN
            use_val := quote_literal( p_new_value );
        ELSE
            use_val := '(x).' || quote_ident( temprec.attname );
        END IF;
        IF use_sql IS NULL THEN·
            use_sql := 'SELECT ' || use_val || ' as ' || quote_ident( temprec.attname );
        ELSE
            use_sql := use_sql || ', ' || use_val || ' as ' || quote_ident( temprec.attname );
        END IF;
    END LOOP;
    use_sql := use_sql || ' FROM ( SELECT $1::[CLASS] as x ) as q';
    use_sql := REPLACE( use_sql, '[CLASS]', quote_ident(p_class) );
    use_sql := 'SELECT o FROM (' || use_sql || ') as o';
    EXECUTE use_sql INTO reply USING p_record;
    RETURN reply;
END;
$$ LANGUAGE plpgsql;

( it is probably possible to write it in simpler way, but I don't care – after all it's not production-ready code ).

How does it work?

Let's see. First I'll check how does some records from pg_ts_config (I choose this table, since it has very little columns, but has some data) look like:

pgdba=# SELECT c FROM pg_ts_config c ORDER BY cfgname ASC LIMIT 3;
          c           
----------------------
 (danish,11,10,3722)
 (dutch,11,10,3722)
 (english,11,10,3722)
(3 ROWS)

OK. And how these records will look after some slight modification?

pgdba=# SELECT set_dynamic_field_in_record(c, 'pg_ts_config', 'cfgname', 'depesz')
    FROM pg_ts_config c
    ORDER BY cfgname ASC LIMIT 3;
 set_dynamic_field_in_record 
-----------------------------
 (depesz,11,10,3722)
 (depesz,11,10,3722)
 (depesz,11,10,3722)
(3 ROWS)

Sweet. Now let's try to actually use these functions in some trigger.

First – test tables. Similar, not with different names of columns, and different number of columns:

CREATE TABLE test1 (
    i   INT4,
    foo TIMESTAMPTZ
);
CREATE TABLE test2 (
    j   INT4,
    q   TEXT,
    bar TIMESTAMPTZ
);

And now let's create trigger function:

CREATE OR REPLACE FUNCTION very_dynamic_trigger() RETURNS TRIGGER AS $$
DECLARE
    add_days INTEGER;
    new_timestamp timestamptz;
    new_record_as_text TEXT;
BEGIN
    add_days := COALESCE( get_dynamic_field_from_record( NEW, TG_TABLE_NAME, TG_ARGV[0] )::INT4, 0 );
    new_timestamp := '2000-01-01 12:34:56 UTC'::timestamptz + '1 day'::INTERVAL * add_days;
 
    new_record_as_text := set_dynamic_field_in_record( NEW, TG_TABLE_NAME, TG_ARGV[1], new_timestamp::TEXT );
    EXECUTE 'SELECT ($1::' || TG_TABLE_NAME || ').*' INTO NEW USING new_record_as_text;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Interesting part is the line with EXECUTE – it converts textual representation of record (from set_dynamic_field_in_record() function) back to actual record.

Logic in the trigger is pretty simple – it takes 2 arguments: name of integer column, and name of timestamp column.

When running, it takes value from integer column, multiplied by ‘1 day', and adds to predefined timestamp (2000-01-01 12:34:56 UTC).

Now, let's create the triggers:

CREATE TRIGGER vdt1
    BEFORE INSERT OR UPDATE ON test1
    FOR EACH ROW EXECUTE PROCEDURE very_dynamic_trigger( 'i', 'foo' );
CREATE TRIGGER vdt2
    BEFORE INSERT OR UPDATE ON test2
    FOR EACH ROW EXECUTE PROCEDURE very_dynamic_trigger( 'j', 'bar' );

I think it's pretty obvious what parameters mean – if not – please check table definitions couple of lines above.

So, let's do some test inserts:

pgdba=# INSERT INTO test1(i, foo) VALUES (1, '2010-09-08 07:06:05 UTC');
pgdba=# INSERT INTO test1(i, foo) VALUES (NULL, '2010-09-08 07:06:05 UTC');
pgdba=# SELECT * FROM test1;
 i |          foo
---+------------------------
 1 | 2000-01-02 13:34:56+01
   | 2000-01-01 13:34:56+01
(2 ROWS)
 
pgdba=# INSERT INTO test2(j, q, bar) VALUES (10, 'depesz', '2010-09-08 07:06:05 UTC');
pgdba=# SELECT * FROM test2;
 j  |   q    |          bar
----+--------+------------------------
 10 | depesz | 2000-01-11 13:34:56+01
(1 ROW)

YEAH!. It works.

Again – please do not use this in production code. It's most likely slow, and there are probably some problem with it – it just serves as an example that it technically can be achieved with PostgreSQL.

One thought on “Stupid tricks – Dynamic updates of fields in NEW in PL/pgSQL”

Leon says:

2011-11-30 at 00:22

Although this is an old article, I do want to point out, that this proof of concept only works on postgres 8.4 or higher, as EXECUTE INTO USING is not supported on earlier version of postgres…

In postgres 8.3 there is another trick, by using:
EXECUTE ‘SELECT (‘ || quote_literal(NEW) || ‘::’ || TG_RELID::regclass || ‘).’ || quote_ident(columnname) INTO var;

The above uses quote_literal(value anyelement), which was introduced in postgres 8.3

After about 10 hours of searching and reading, I will have to conclude that selecting dynamic fields from RECORD types in postgres 8.2 or older is just not possible… To bad for my customer who just happens to use 8.2 and needs dynamic field selection 😉

Comments are closed.