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

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