Re: Verify a record has a column in a plpgsql trigger

[Mike Ginsburg <mginsburg@xxxxxxxxxxxxxxxxxxxxxxx>]

Merlin Moncure wrote:

On Thu, Feb 4, 2010 at 11:41 AM, Mike Ginsburg
<mginsburg@xxxxxxxxxxxxxxxxxxxxxxx> wrote:
  

I have a plpgsql function that serves as a change log for a few tables in my
db (8.4.2).  In most of the tables that I am logging, there is an "editor"
column that stores the ID of the user who made the change, so as part of the
function I set

editor := NEW.editor;

There are a few of the tables that don't store editor, in which case I am ok
with inserting it into the log as NULL.  The problem is I can't seem to come
up with a conditional to see if NEW has a column named "editor".
    

There's no way to do query now/old for columns directly in pl/pgsql.
Some alternatives:

1) use begin/exception/end to try and set it, and catch the error.
would likely be the best route but be aware that functions with
exception handlers have a higher cost than those without
2) query system catalogs or information schema
3) build a cache (a list of tables that support editor in a table you query)

If it was me, I'd do #3 if performance was critical, otherwise #1.

merlin
  

Thanks for the help!  I'll look into the exceptions to see how expensive they are.  On a related note, I was just told by our sysadmins that pg 8.4 might not be installed by the time this needs to be rolled out, leaving me in a bind since I have been using "EXECUTE ... USING" queries.  A sample of my trigger is below:

  FOR colRow IN SELECT attname FROM pg_catalog.pg_attribute WHERE attnum > 0 AND attrelid = TG_RELID LOOP
      EXECUTE 'SELECT ($1).' || colRow.attname || '::text' INTO n USING NEW;
      EXECUTE 'SELECT ($1).' || colRow.attname || '::text' INTO o USING OLD;
      IF n <> o THEN
        q := 'INSERT INTO change_log (...) VALUES (...);
        EXECUTE q;
      END IF;
    END LOOP;

Any insight on a way I can grab NEW.(colRow.attname) without EXECUTE USING?

Mike Ginsburg
mginsburg@xxxxxxxxxxxxxxxxxxxxxxx