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

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$
;

CodePudding user response：

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