Date: Tue, 11 Jun 2024 21:58:43 +0200
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: veem v <veema0000@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: Does trigger only accept functions?
Message-ID: <Zmisc69SLafs-YVW@depesz.com>
Reply-To: depesz@depesz.com
References: <CAB+=1TWT1_kKdBtuWvLBKJ1hkjxsaRZfMQADQU7bsVtat9q-LA@mail.gmail.com>
 <Zmg2GIso2Lb4r_2m@depesz.com>
 <CAB+=1TUWFJ_9BsqVZkj+3V18WxpAZojzhbOLN1kGzUYqx=TLfQ@mail.gmail.com>
 <Zmid1OlE-4j0c2l1@depesz.com>
 <CAB+=1TWb2ASHes8ynVcWs9DAuOgKsY-m-EAJfNwqE0=F=03Uug@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <CAB+=1TWb2ASHes8ynVcWs9DAuOgKsY-m-EAJfNwqE0=F=03Uug@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/Zmisc69SLafs-YVW%40depesz.com>
Precedence: bulk

On Wed, Jun 12, 2024 at 12:50:27AM +0530, veem v wrote:
> My apology, if interpreting it wrong way. It doesn't make much difference
> though, but do you mean something like below?

if you really have totally different structures across all tables, and
you don't want to use pgaudit (which is the best solution), and you
don't want to have custom function per table, then i'd use hstore
datatype, and store all deleted rows, regardless of where they came
from, in single log table (potentially partitioned).

Something like:

create table deleted_rows (
    id int8 generated always as identity primary key,
    source_schema text,
    source_table text,
    deleting_user text,
    deleted_at timestamptz,
    deleted_row hstore
);

create function log_deletes() returns trigger as $$
DECLARE
BEGIN
    INSERT INTO deleted_rows (source_schema, source_table, deleting_user, deleted_at, deleted_row)
        VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, CURRENT_USER, now(), hstore(OLD) );
    return OLD;
END;
$$ language plpgsql;

and then just:

create trigger x after delete on tablex for each row execute function log_deletes();

or something like this, if I made any typos.

Best regards,

depesz