1-- Step 1: Create the function
2CREATE OR REPLACE FUNCTION trigger_set_timestamp()
3RETURNS TRIGGER AS $$
4BEGIN
5 NEW.updated_at = NOW();
6 RETURN NEW;
7END;
8$$ LANGUAGE plpgsql;
9
10-- Step 2: Create the table
11CREATE TABLE my_table (
12 id SERIAL NOT NULL PRIMARY KEY,
13 content TEXT,
14 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
15 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
16);
17
18-- Step 3: Create the trigger
19CREATE TRIGGER set_timestamp
20BEFORE UPDATE ON my_table
21FOR EACH ROW
22EXECUTE PROCEDURE trigger_set_timestamp();
23
24-- Step 4: Profit
25-- Now we can insert and update rows in the table, and both
26-- the created_at and updated_at columns will be saved correctly :)
1CREATE OR REPLACE FUNCTION public.set_current_timestamp_updated_at()
2 RETURNS trigger
3 LANGUAGE plpgsql
4AS $function$
5DECLARE
6 _new record;
7BEGIN
8 _new := NEW;
9 _new."updated_at" = NOW();
10 RETURN _new;
11END;
12$function$
13
14
15CREATE TRIGGER set_updated_at
16BEFORE UPDATE ON your_table
17FOR EACH ROW
18EXECUTE FUNCTION set_current_timestamp_updated_at();
19