h74JijHJ

1prefix_last_modified timestamp without time zone NOT NULL DEFAULT  (clock_timestamp() AT TIME ZONE 'UTC')
2	
3CREATE OR REPLACE FUNCTION update_modified_column() 
4RETURNS TRIGGER AS $$
5BEGIN
6    NEW.modified = now();
7    RETURN NEW; 
8END;
9$$ language 'plpgsql';
10	
11CREATE OR REPLACE FUNCTION update_modified_column(varchar column)   
12    RETURNS TRIGGER AS $$
13    BEGIN
14        NEW.column = now();
15        RETURN NEW; 
16    END;
17$$ language 'plpgsql';
18	
19DROP TABLE mdt;
20
21CREATE TABLE mdt (
22    id      int4,
23    idesc       text,
24    moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
25);
26
27CREATE TRIGGER mdt_moddatetime
28    BEFORE UPDATE ON mdt
29    FOR EACH ROW
30    EXECUTE PROCEDURE moddatetime (moddate);
31
32INSERT INTO mdt VALUES (1, 'first');
33INSERT INTO mdt VALUES (2, 'second');
34INSERT INTO mdt VALUES (3, 'third');
35
36SELECT * FROM mdt;
37
38UPDATE mdt SET id = 4
39    WHERE id = 1;
40UPDATE mdt SET id = 5
41    WHERE id = 2;
42UPDATE mdt SET id = 6
43    WHERE id = 3;
44
45SELECT * FROM mdt;
46	
47CREATE EXTENSION moddatetime;
48
49CREATE TRIGGER mdt_table
50  BEFORE UPDATE ON table
51  FOR EACH ROW
52  EXECUTE PROCEDURE moddatetime (prefix_last_modified);
53	
54--drop table if exists t1;
55--drop table if exists t2;
56--drop function if exists foo();
57create table t1(x int, q int);
58create table t2(y int, q int);
59
60create function foo() returns trigger language plpgsql as $foo$
61begin
62  raise info '%', tg_argv[0];
63  raise info '%', new; 
64  execute $$select (b).* from (select $1 #= hstore($2||'=>666')) as a(b)$$ using new, tg_argv[0] into new;
65  raise info '%', new;
66  return new;
67end $foo$;
68
69create trigger tg_def before insert on t1 for each row execute procedure foo('x');
70create trigger tg_def before insert on t2 for each row execute procedure foo('y');
71
72nd@postgres=# insert into t1 values(1,2);
73INFO:  x
74INFO:  (1,2)
75INFO:  (666,2)
76INSERT 0 1
77nd@postgres=# insert into t2 values(1,3);
78INFO:  y
79INFO:  (1,3)
80INFO:  (666,3)
81INSERT 0 1
82nd@postgres=# select * from t2;
83╔═════╤═══╗
84║  y  │ q ║
85╠═════╪═══╣
86║ 666 │ 3 ║
87╚═════╧═══╝