Обсуждение: Dynamic Log tigger (plpgsql)

Hi

I want to implement a trigger-function witch can fill the following table.
Each data manipulation (INSERT, UPDATE or DELETE) gets logged.
The function should work as trigger on diffrent tables.

CREATE TABLE logtable (
  operation        CHAR(6) CHECK (change_type IN ('DELETE', 'INSERT', 'UPDATE')),
  tablename        VARCHAR,
  rowid            INTEGER,  -
  touched_columns  VARCHAR[]
);

My Problem is in the last Column (touched_columns).
If it was an UPDATE Operation, I just need to know witch columns changed. (I am not iterrestet in the old or new value)
=> IF OLD.columnName != NEW.columnName, it has changed.



My Question:
How can I do "OLD.columnName != NEW.columnName" if I don't know what the
columnNames are at Compile Time?
I have the columnName in a variable.


Thx for help.
Noah

On 6/16/07, Noah Heusser <noah@heussers.ch> wrote:
> Hi
>
> I want to implement a trigger-function witch can fill the following table.
> Each data manipulation (INSERT, UPDATE or DELETE) gets logged.
> The function should work as trigger on diffrent tables.
>
> CREATE TABLE logtable (
>   operation        CHAR(6) CHECK (change_type IN ('DELETE', 'INSERT', 'UPDATE')),
>   tablename        VARCHAR,
>   rowid            INTEGER,  -
>   touched_columns  VARCHAR[]
> );
>
> My Problem is in the last Column (touched_columns).
> If it was an UPDATE Operation, I just need to know witch columns changed. (I am not iterrestet in the old or new
value)
> => IF OLD.columnName != NEW.columnName, it has changed.
>
>
>
> My Question:
> How can I do "OLD.columnName != NEW.columnName" if I don't know what the
> columnNames are at Compile Time?
> I have the columnName in a variable.
>
>
> Thx for help.
> Noah
>

Are you trying to do this from a plpgsql function? If so then I think
you should try to do this from a C function.

With C functions you will get more control over the new and old
versions of the tuple since you get their pointers via
TriggerData->tg_trigtuple (old tuple) and TriggerData->tg_newtuple
(new tuple).

--
Sibte Abbas
EnterpriseDB http://www.enterprisedb.com

>> How can I do "OLD.columnName != NEW.columnName" if I don't know what the
>> columnNames are at Compile Time?
>> I have the columnName in a variable.
>
> Are you trying to do this from a plpgsql function? If so then I think
> you should try to do this from a C function.
>
> With C functions you will get more control over the new and old
> versions of the tuple since you get their pointers via
> TriggerData->tg_trigtuple (old tuple) and TriggerData->tg_newtuple
> (new tuple).
>
I think this would work. And if there is no other possibility i will do
that.
But as I saw, it is necessary to have Sysadmin rights, if you want to
add a C function.
If possible i want to do it as a regular DB-Owner.

> My Question:
> How can I do "OLD.columnName != NEW.columnName" if I don't know what the
> columnNames are at Compile Time?
> I have the columnName in a variable.
>

I suggest you use plpython. In this case you'll be able to do it.

TD['old'][colNameVar] != TD['new'][colNameVar]

--
Regards,
Sergey Konoplev

Sergey Konoplev schrieb:
>> My Question:
>> How can I do "OLD.columnName != NEW.columnName" if I don't know what the
>> columnNames are at Compile Time?
>> I have the columnName in a variable.
>>
>
> I suggest you use plpython. In this case you'll be able to do it.
>
> TD['old'][colNameVar] != TD['new'][colNameVar]
>
thx, you are right. These Languages are trusted like pgsql,

Did it in Perl:

      foreach $key (keys %{$_TD->{old}}) {
        if($_TD->{old}{$key} ne $_TD->{new}{$key}){

        }
      }

On Jun 16, 2007, at 6:26 AM, Noah Heusser wrote:
> I want to implement a trigger-function witch can fill the following
> table.
> Each data manipulation (INSERT, UPDATE or DELETE) gets logged.
> The function should work as trigger on diffrent tables.
>
> CREATE TABLE logtable (
>  operation        CHAR(6) CHECK (change_type IN ('DELETE',
> 'INSERT', 'UPDATE')),

Note that that field will take 12 bytes in 8.2, and assuming that
varvarlena is in 8.3, 8 bytes there (or is varvarlena byte-aligned?)

You might be better going with "char" (with the double-quotes) and
'D', 'I', and 'U'.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

>> How can I do "OLD.columnName != NEW.columnName" if I don't know what the
>> columnNames are at Compile Time?
>> I have the columnName in a variable.
>
> Are you trying to do this from a plpgsql function? If so then I think
> you should try to do this from a C function.
>
> With C functions you will get more control over the new and old
> versions of the tuple since you get their pointers via
> TriggerData->tg_trigtuple (old tuple) and TriggerData->tg_newtuple
> (new tuple).
>
I think this would work. And if there is no other possibility i will do
that.
But as I saw, it is necessary to have Sysadmin rights, if you want to
add a C function.
If possible i want to do it as a regular DB-Owner.