Thread: Verify a record has a column in a plpgsql trigger
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". Any help is greatly appreciated. Thanks. Mike Ginsburg
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
Merlin Moncure wrote:
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?
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: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
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
Mike Ginsburg <mginsburg@collaborativefusion.com> wrote: > [...] > 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? Wouldn't it be *much* easier to just have /two/ trigger functions? Your "editor" columns probably don't pop up and disappear randomly. Tim
On Thu, Feb 4, 2010 at 7:36 PM, Tim Landscheidt <tim@tim-landscheidt.de> wrote: >> Any insight on a way I can grab NEW.(colRow.attname) without EXECUTE USING? > > Wouldn't it be *much* easier to just have /two/ trigger > functions? Your "editor" columns probably don't pop up and > disappear randomly. or, you could pass an argument to the trigger function from 'create trigger'...you have to have one for each table anyways, and you could wrap the trigger creation with some dyna-sql that looks up the editor field and sets the argument appropriately, merlin
Merlin Moncure wrote:
Thanks again for all the help. I started playing around with custom session vars and it seems to be working well.On Thu, Feb 4, 2010 at 7:36 PM, Tim Landscheidt <tim@tim-landscheidt.de> wrote:Any insight on a way I can grab NEW.(colRow.attname) without EXECUTE USING?Wouldn't it be *much* easier to just have /two/ trigger functions? Your "editor" columns probably don't pop up and disappear randomly.or, you could pass an argument to the trigger function from 'create trigger'...you have to have one for each table anyways, and you could wrap the trigger creation with some dyna-sql that looks up the editor field and sets the argument appropriately, merlin
Mike Ginsburg mginsburg@collaborativefusion.com