public inbox for [email protected]
help / color / mirror / Atom feedRe: Does trigger only accept functions?
4+ messages / 3 participants
[nested] [flat]
* Re: Does trigger only accept functions?
@ 2024-06-11 18:56 hubert depesz lubaczewski <[email protected]>
2024-06-11 19:20 ` Re: Does trigger only accept functions? veem v <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: hubert depesz lubaczewski @ 2024-06-11 18:56 UTC (permalink / raw)
To: veem v <[email protected]>; +Cc: pgsql-general <[email protected]>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Does trigger only accept functions?
2024-06-11 18:56 Re: Does trigger only accept functions? hubert depesz lubaczewski <[email protected]>
@ 2024-06-11 19:20 ` veem v <[email protected]>
2024-06-11 19:46 ` Re: Does trigger only accept functions? Adrian Klaver <[email protected]>
2024-06-11 19:58 ` Re: Does trigger only accept functions? hubert depesz lubaczewski <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: veem v @ 2024-06-11 19:20 UTC (permalink / raw)
To: [email protected]; +Cc: pgsql-general <[email protected]>
On Wed, 12 Jun 2024 at 00:26, hubert depesz lubaczewski <[email protected]>
wrote:
>
>
> 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
>
>
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
RAISE EXCEPTION 'Audit table not defined for %', TG_TABLE_NAME;
END IF;
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Does trigger only accept functions?
2024-06-11 18:56 Re: Does trigger only accept functions? hubert depesz lubaczewski <[email protected]>
2024-06-11 19:20 ` Re: Does trigger only accept functions? veem v <[email protected]>
@ 2024-06-11 19:46 ` Adrian Klaver <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Adrian Klaver @ 2024-06-11 19:46 UTC (permalink / raw)
To: veem v <[email protected]>; [email protected]; +Cc: pgsql-general <[email protected]>
On 6/11/24 12:20, veem v wrote:
>
>
> On Wed, 12 Jun 2024 at 00:26, hubert depesz lubaczewski
> <[email protected] <mailto:[email protected]>> wrote:
>
>
>
> 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 <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?
>
> 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
> RAISE EXCEPTION 'Audit table not defined for %', TG_TABLE_NAME;
> END IF;
>
> RETURN OLD;
> END;
> $$ LANGUAGE plpgsql;
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 protected]
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Does trigger only accept functions?
2024-06-11 18:56 Re: Does trigger only accept functions? hubert depesz lubaczewski <[email protected]>
2024-06-11 19:20 ` Re: Does trigger only accept functions? veem v <[email protected]>
@ 2024-06-11 19:58 ` hubert depesz lubaczewski <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: hubert depesz lubaczewski @ 2024-06-11 19:58 UTC (permalink / raw)
To: veem v <[email protected]>; +Cc: pgsql-general <[email protected]>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-06-11 19:58 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-11 18:56 Re: Does trigger only accept functions? hubert depesz lubaczewski <[email protected]>
2024-06-11 19:20 ` veem v <[email protected]>
2024-06-11 19:46 ` Adrian Klaver <[email protected]>
2024-06-11 19:58 ` hubert depesz lubaczewski <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox