public inbox for [email protected]  

help / color / mirror / Atom feed

From: hubert depesz lubaczewski <[email protected]>
To: veem v <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Does trigger only accept functions?
Date: Tue, 11 Jun 2024 21:58:43 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAB+=1TWb2ASHes8ynVcWs9DAuOgKsY-m-EAJfNwqE0=F=03Uug@mail.gmail.com>
References: <CAB+=1TWT1_kKdBtuWvLBKJ1hkjxsaRZfMQADQU7bsVtat9q-LA@mail.gmail.com>
	<[email protected]>
	<CAB+=1TUWFJ_9BsqVZkj+3V18WxpAZojzhbOLN1kGzUYqx=TLfQ@mail.gmail.com>
	<[email protected]>
	<CAB+=1TWb2ASHes8ynVcWs9DAuOgKsY-m-EAJfNwqE0=F=03Uug@mail.gmail.com>

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

view thread (4+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Does trigger only accept functions?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox