How to log 'new table' from update trigger in postgresql?

Question

I have a complex trigger which executes on update of a table.
I get what I want when I execute it's body with 'new table' replaced with existing table, but it messes things up when it called by update.
I want to debug this and I want to start by viewing what my trigger gets as 'new table'. How can I look on it?

CREATE TRIGGER foo_trigger AFTER
UPDATE
    ON
    public.table1 REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION foo()

 CREATE OR REPLACE FUNCTION public.foo()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
    BEGIN

    *do things with new_table*
    
    RETURN NULL;
    END;
$function$
;

You can start by posting the trigger and trigger function. Nobody will be able to help you without more information — Patrick
– Patrick, Commented Oct 26, 2022 at 8:14
@Patrick added some code. I didn't thought that it is needed because my question is not about debug my code for me but about how to do it by myself. — Dmitry
– Dmitry, Commented Oct 26, 2022 at 8:47
Your question states "I want to debug", then you comment "my question is not about debug". You claim when the function executes it does what it should but when called by an update, it doesn't - yet trigger launches the function only after update. NEW isn't the entire new table, it's the new records coming in. You can create a mirror table where you dump it: create table public.foo_trigger_test as table public.table1 with no data; then, in the trigger function: insert into public.foo_trigger_test select * from new_table; — Zegarek
– Zegarek, Commented Oct 26, 2022 at 10:09
@Zegarek english isn't my native, sorry. I said not about debug my code for me and mean that I just needed to know a way to copy this new_table somewhere I can read it from. And your code works, thanks! — Dmitry
– Dmitry, Commented Oct 26, 2022 at 10:22

Zegarek · Accepted Answer · 2022-10-26 10:35:23Z

You can dump it to a table and check its contents after your test update.

create table public.table1 (col1 integer);
insert into public.table1 values (0),(1),(2),(3);

create table public.foo_trigger_test as table public.table1 with no data;

CREATE OR REPLACE FUNCTION public.foo()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
    BEGIN
        insert into public.foo_trigger_test select a.* from new_table a;
    RETURN NULL;
    END;
$function$;

CREATE TRIGGER foo_trigger 
  AFTER UPDATE
  ON public.table1 
  REFERENCING NEW TABLE AS new_table 
  FOR EACH STATEMENT 
  EXECUTE FUNCTION foo();

Now an update triggering the function will dump a copy of what it got in NEW (aliased by new_table).

update public.table1 set col1=99 where col1 between 0 and 2;
table public.table1;
-- col1
--------
--    3
--   99
--   99
--   99
--(4 rows)
table public.foo_trigger_test;
-- col1
--------
--   99
--   99
--   99
--(3 rows)

Example

Collectives™ on Stack Overflow

How to log 'new table' from update trigger in postgresql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related