Обсуждение: 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:

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

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:

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
 

Thanks again for all the help.  I started playing around with custom session vars and it seems to be working well.

Mike Ginsburg
mginsburg@collaborativefusion.com