Subject: Re: Does trigger only accept functions?

From: hubert depesz lubaczewski <[email-masked]>
Date: Tue, 11 Jun 2024 20:56:20 +0200

On Wed, Jun 12, 2024 at 12:19:55AM +0530, veem v wrote:
> CREATE OR REPLACE FUNCTION log_deletes()
> RETURNS TRIGGER AS $$
> BEGIN
>     IF TG_TABLE_NAME = 'source_table1' THEN
>         INSERT INTO delete_audit1 ( col1, col2, col3)
>         VALUES (OLD.col1, OLD.col2, OLD.col3);
>     ELSIF TG_TABLE_NAME = 'source_table2' THEN
>         INSERT INTO delete_audit2 ( col4, col5, col6)
>         VALUES (OLD.col4, OLD.col5, OLD.col6);
>     -- Add more conditions for other tables
>     ELSE
>         RAISE EXCEPTION 'Audit table not defined for %', TG_TABLE_NAME;
>     END IF;
>     RETURN OLD;
> END;
> $$ LANGUAGE plpgsql;

No, I meant building dynamic queries and then EXECUTE-ing, like docs
show:
https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Best regards,

depesz

---

From: veem v <[email-masked]>
Date: Wed, 12 Jun 2024 00:50:27 +0530

> https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
My apology, if interpreting it wrong way. It doesn't make much difference
though, but do you mean something like below?

CREATE OR REPLACE FUNCTION log_deletes()
RETURNS TRIGGER AS $$
DECLARE
    audit_table_name TEXT;
    audit_query TEXT;
BEGIN
    IF TG_TABLE_NAME = 'source_table1' THEN
        audit_table_name := 'delete_audit1';
        audit_query := 'INSERT INTO ' || audit_table_name || ' (record_id,
delete_timestamp, col1, col2, col3) VALUES ( $2, $3, $4)';
        EXECUTE audit_query USING OLD.id, OLD.col1, OLD.col2, OLD.col3;
    ELSIF TG_TABLE_NAME = 'source_table2' THEN
        audit_table_name := 'delete_audit2';
        audit_query := 'INSERT INTO ' || audit_table_name || ' ( col4,
col5, col6) VALUES ( $2, $3, $4)';
        EXECUTE audit_query USING OLD.id, OLD.col4, OLD.col5, OLD.col6;

    ELSE

    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 11 Jun 2024 12:46:24 -0700

On 6/11/24 12:20, veem v wrote:
> 
> 
> On Wed, 12 Jun 2024 at 00:26, hubert depesz lubaczewski 
> <[email-masked] <mailto:[email-masked]>> wrote:
> 
> 
> 
>     https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN <https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN>
> 
>     Best regards,
> 
>     depesz
> 
> 
> My apology, if interpreting it wrong way. It doesn't make much 
> difference though, but do you mean something like below?
> 
>          audit_query := 'INSERT INTO ' || audit_table_name || ' 
> (record_id, delete_timestamp, col1, col2, col3) VALUES ( $2, $3, $4)';

I'm guessing depesz meant using TG_TABLE_NAME to pull column information 
from:

https://www.postgresql.org/docs/current/catalog-pg-attribute.html

and use that to build the INSERT query. The issue with dynamic or a 
fixed SQL is going to be with audit_query, in particular 
audit_table_name := 'delete_audit2. If your source tables change, add or 
delete columns or column types change, your audit table will need to 
change to match.

One possible solution is something I outlined here:

https://aklaver.org/wordpress/2021/12/07/postgres-and-json/

Other folks have done similar things, you can search on

postgresql audit tables using json

for alternatives.

-- 
Adrian Klaver
[email-masked]

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Tue, 11 Jun 2024 21:58:43 +0200

> 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