Trigger function check if all NEW.* fields exist in another table

Question

In PostgreSQL I need to code a trigger function that checks if all the fields in NEW.* exist in another table that has the exact same name as the table that fired the trigger but appended with "_hv".

For example, if the table my_example fires the trigger I need to check if all the fields in my_example exist in my_example_hv, and if it does not, alter my_example_hv to add the fields that are missing.

I am very new to plpgsql and I starting trying to code it with no success:

CREATE OR REPLACE FUNCTION trigger_hv()
  RETURNS trigger AS
$BODY$ 
DECLARE
    typeoffield character varying;      
BEGIN

FOR field IN NEW LOOP
      IF NEW.field not exists ON TG_TABLE_NAME||'_hv' THEN
        typeoffield := typeof(NEW.columns); -- of course does not work
        EXECUTE 'ALTER '||TG_RELNAME||'_hv ADD COLUMN '||NEW.column ||' '||typeoffield; 
      END IF;
END LOOP;

RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql;

I think I need some expert help ...

Answer 1

Your attempt fails for multiple reasons. First of all a row is not an array. This construct is just not possible:

FOR field IN NEW LOOP ...

But there is more.

I am not sure I like the general idea. This kind of trigger would run for at least every statement (don't use a row-level trigger for this!), which is quite a bit of overhead. It is also error-prone to put DDL commands in a trigger. Especially if you are a beginner.

That said, here is a proof of concept:

Trigger function:

CREATE OR REPLACE FUNCTION trigger_hv()
  RETURNS trigger AS
$func$
DECLARE
    _sql text;
BEGIN
   SELECT INTO _sql
         'ALTER TABLE ' || quote_ident(TG_RELNAME || '_hv') || ' ADD COLUMN '
       || string_agg(quote_ident(attname) || ' ' || att_type, ', ADD COLUMN ')
   FROM  (
      SELECT attname, format_type(atttypid, atttypmod) AS att_type
      FROM   pg_attribute
      WHERE  attrelid = TG_TABLE_NAME::regclass
      AND    NOT attisdropped   -- no dropped (dead) columns
      AND    attnum > 0         -- no system columns
      ) a
   LEFT   JOIN (
      SELECT attname
      FROM   pg_attribute
      WHERE  attrelid = (TG_TABLE_NAME || '_hv')::regclass
      AND    NOT attisdropped
      AND    attnum > 0
      ) b USING (attname)  -- ignoring data type!
   WHERE  b.attname IS NULL;

   IF _sql IS NOT NULL THEN
      EXECUTE _sql;
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER trg_foo_hv
BEFORE INSERT ON foo
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_hv();

SQL Fiddle.

This is a basic proof of concept. It does not check for matching data types and ignores things like schema search_path, NOT NULL constraints or COLLATION.

Related (with more explanation and links):

• Change column type from varchar to text in all tables at once
• Update multiple columns in a trigger function in plpgsql
• Select rows where value of second column is not present in first column