Iteration over record in PL/pgSQL

Hello

A iteration over record in PL/pgSQL is old well known problem. When we try to write a general triggers in PL/pgSQL we can find a break, because we are not able to iterate over record. There was a some workarounds, but these solutions are simply slow and complex. So I wrote a set of functions that can helps.

First functions is record_expand. This function is similar to unnest function, but related object is record:

 
postgres=# select * from pst.record_expand(row('10',null,'Ahoj', current_date)); 
 name |   value    |   typ    
------+------------+--------- 
 f1   | 10         | unknown 
 f2   |            | unknown 
 f3   | Ahoj       | unknown 
 f4   | 2010-12-10 | date 
(4 rows) 

Now isn't problem to write general trigger for detecting a changed colums:

CREATE TABLE foo(a int, b int, c text, d int); 
 
CREATE OR REPLACE FUNCTIO update_trg_func() 
RETURNS trigger as $$ 
DECLARE r record; 
BEGIN 
  FOR r IN SELECT n.name, o.value as oldval, n.value as newval 
              FROM pst.record_expand(new) n, 
                   pst.record_expand(old) o 
             WHERE n.name = o.name 
               AND n.value IS DISTINCT FROM o.value 
  LOOP  
    RAISE NOTICE '% % %', r.name, r.oldval, r.newval; 
END LOOP; 
RETURN NULL; 
END; 
$$ LANGUAGE plpgsql; 
 
CREATE TRIGGER update_trg 
  AFTER UPDATE ON foo 
  FOR EACH ROW EXECUTE PROCEDURE update_trg_func(); 
 
postgres=# UPDATE foo SET c = 'Pavel', a = 40; 
NOTICE:  a 30 40 
NOTICE:  c omega Pavel 
UPDATE 1 

Next function allows update of any dynamic record. This function is record_set_fields: It's a variadic functions - you can enter a fields and values to change:

postgres=# SELECT pst.record_set_fields(row(0,0,'',0)::foo, 'd', 100, 'c', 'Hello');
  record_set_fields
-------------------
  (0,0,Hello,100)

It can be used for dynamic initialization of wide tables - for some OLAP purposes:

  
 CREATE TABLE t(a0 int, a1 int, a2 int, a3 int, a4 int, a5 int); 
 
-- set all null fields ax on value -1000 
 
CREATE OR REPLACE FUNCTION insert_trg_func()
RETURNS trigger as $$
DECLARE name text;
BEGIN
   FOR name IN SELECT x.name 
                  FROM pst.record_expand(new) x 
                 WHERE x.value IS NULL AND x.name LIKE 'a%'
   LOOP  
     new = pst.record_set_fields(new, name, -1000);
   END LOOP; 
   RETURN new;
END; 
$$ LANGUAGE plpgsql;  

CREATE TRIGGER insert_trg 
   BEFORE INSERT ON t 
   FOR EACH ROW EXECUTE PROCEDURE insert_trg_func(); 
 
postgres=# INSERT INTO t(a3,a5) VALUES(100,100);
INSERT 0 1
postgres=# SELECT * FROM t;
  a0   |  a1   |  a2   | a3  |  a4   | a5  
-------+-------+-------+-----+-------+-----
 -1000 | -1000 | -1000 | 100 | -1000 | 100
(1 row)

Last function is record_get_field. Its returns a value of entered field.

CREATE OR REPLACE FUNCTION insert_trg_func() 
RETURNS TRIGGER AS $$ 
DECLARE name text; 
BEGIN
  FOR i IN 0..5 LOOP
    IF pst.record_get_field(new, 'a'||i) IS NULL THEN
      new := pst.record_set_fields(new, 'a'||i, -1000); 
    END IF; 
  END LOOP; r
  RETURN new; 
END; 
$$ LANGUAGE plpgsql; 

These package is available from pgfoundry http://pgfoundry.org/frs/shownotes.php?release_id=1749.