2

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 ...

  • So my_example_hv can have fewer columns. Can there also be extra columns and would that matter? Also, your version of Postgres, please. And please clarify a few things: the title asks to check for existence, while you go on talking about adding columns, which is something different. Also, what does your trigger look like? It find it hard to imagine a useful application for this trigger function. Since the trigger has to be created for a specific table, the trigger would be done and useless after the first invocation. Or are there other forces constantly dropping columns from your tables? – Erwin Brandstetter Mar 25 '15 at 16:46
  • my_example_hv will have 4 more fields at the end when hv table is created, beacuse it is an exact copy of my_example plus four more fields, but if I insert new fields in my_example, imagine 6 new fields, those new fields need to be added to my_example_hv too... Postgresql version is 8.4 (I know its obsolete but I have some old software that needs that version). – Egidi Mar 25 '15 at 17:15
  • I have insert, update and delte triggers that dill the my_example_hv with the modifications being made to my_example_hv. I use it to have a historic with all the changes being made in the table, so that later on, I can perfectly load an exact picture of what was at certain date. My issue comes when new fields are added to my_example table, those new fields need to be added too to my_example_hv. – Egidi Mar 25 '15 at 17:18
4

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):

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.