Add REFERENCING tables in trigger

[albrov]

Hi. Postgres has a construct in the trigger creation syntax:

[ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]

but there is no implementation in the library. In the project, we often use this functionality. Please add implementation to the library Мy version of implementation

25a26
>                  ref_new_table=None, ref_old_table=None,
42a44,45
>         :param ref_new_table: REFERENCING clause name for NEW TABLE (from tgnewtable)
>         :param ref_old_table: REFERENCING clause name for OLD TABLE (from tgoldtable)
87a91,92
>         self.ref_new_table = ref_new_table
>         self.ref_old_table = ref_old_table
105c110,111
<                    obj_description(t.oid, 'pg_trigger') AS description, t.oid
---
>                    obj_description(t.oid, 'pg_trigger') AS description,
>                    tgnewtable AS ref_new_table, tgoldtable AS ref_old_table, t.oid
142c148,149
<             inobj.pop('condition', None), args)
---
>             inobj.pop('condition', None), args,
>             inobj.pop('ref_new_table', None),inobj.pop('ref_old_table', None))
174a182,186
>         if self.ref_new_table is None:
>             del dct['ref_new_table']
>         if self.ref_old_table is None:
>             del dct['ref_old_table']
> 
207c219,227
<         return ["CREATE %sTRIGGER %s\n    %s %s ON %s%s\n    FOR EACH %s"
---
>         refer = ''
>         if self.ref_new_table is not None or self.ref_old_table is not None:
>            refer="REFERENCING "
>            if self.ref_new_table is not None:
>                refer += "NEW TABLE AS %s " % self.ref_new_table
>            if self.ref_old_table is not None:
>                refer+="OLD TABLE AS %s " % self.ref_old_table
>            refer+="\n"
>         return ["CREATE %sTRIGGER %s\n    %s %s ON %s%s\n  %s  FOR EACH %s"
211c231
<                     self.level.upper(), cond, procname, args)]
---
>                     refer, self.level.upper(), cond, procname, args)]
222c242,243
<            or self.level != inobj.level or self.timing != inobj.timing:
---
>            or self.level != inobj.level or self.timing != inobj.timing \
>            or self.ref_new_table != inobj.ref_new_table or self.ref_old_table != inobj.ref_old_table:

[jmafc]

Although the PG docs have a couple of examples of CREATE TRIGGER ... REFERENCING, it would be helpful if you could also provide a couple of examples of the setup needed to invoke such a statement, so that they can be used in tests for covering the feature. They don't need to be too complex or use realistic names, just the basic CREATE TABLE/FUNCTIONs needed. Thanks.

[albrov]

I forgot about the example.....

CREATE FUNCTION public.test_tr_insert()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
  qnt int;
BEGIN
    SELECT count(*) INTO qnt FROM inserted;
    RAISE NOTICE 'qnt = %', qnt;
    RETURN NULL;
END;
$BODY$;

CREATE  FUNCTION public.test_tr_delete()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
  qnt int;
BEGIN
    SELECT count(*) INTO qnt FROM deleted;
    RAISE NOTICE 'qnt = %', qnt;
        RETURN NULL;
END;
$BODY$;

CREATE TABLE test(c1 character varying(20))

CREATE TRIGGER test_tr_insert
    AFTER INSERT
    ON public.test
    REFERENCING NEW TABLE AS inserted
    FOR EACH STATEMENT
    EXECUTE FUNCTION public.test_tr_insert();

CREATE TRIGGER test_tr_delete
    AFTER DELETE
    ON public.test
    REFERENCING OLD TABLE AS deleted
    FOR EACH STATEMENT
    EXECUTE FUNCTION public.test_tr_delete();

REFERENCING can contain both tables

    ......
    REFERENCING NEW TABLE AS inserted OLD TABLE AS deleted
    ......

launch

insert into test(c1) VALUES('q'), ('w'), ('e');
result:
NOTICE:  qnt = 3
INSERT 0 3

delete from test where c1 = 'q' OR c1 = 'w'
result:
NOTICE:  qnt = 2
DELETE 2

yaml for table my implementation

table test:
  columns:
  - c1:
      statistics: 0
      type: character varying(20)
  owner: dmfms
  triggers:
    test_tr_delete:
      events:
      - delete
      level: statement
      procedure: public.test_tr_delete
      ref_old_table: deleted
      timing: after
    test_tr_insert:
      events:
      - insert
      level: statement
      procedure: public.test_tr_insert
      ref_new_table: inserted
      timing: after

[jmafc]

Fixed by 3a385ba. Adapted mostly from your code above, but using more synthetic tests.