Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux