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

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