Merlin Moncure wrote:
On Thu, Feb 4, 2010 at 11:41 AM, Mike Ginsburg
<mginsburg@collaborativefusion.com> 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@collaborativefusion.com