Re: 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(at)heussers(dot)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.

>> 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(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)