Auto update column

auto_update_column.sql

CREATE OR REPLACE FUNCTION jobs.trigger_update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated = NOW()::timestamp;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- DROP TABLE jobs.job;

CREATE TABLE IF NOT EXISTS jobs.job (
    id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
    deletion_mark bool NULL DEFAULT false;
    creator_id text NOT NULL,
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    job_data jsonb NOT NULL,
    CONSTRAINT job_pkey PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS job_deletion_mark_idx ON jobs.job USING btree (deletion_mark);
CREATE INDEX IF NOT EXISTS job_creator_id_idx ON jobs.job USING btree (creator_id);
CREATE INDEX IF NOT EXISTS job_created_idx ON jobs.job USING btree (created);
CREATE INDEX IF NOT EXISTS job_updated_idx ON jobs.job USING btree (updated);

COMMENT ON COLUMN jobs.job.id IS 'Идентификатор элемента';
COMMENT ON COLUMN jobs.job.deletion_mark IS 'Пометка на удаление';
COMMENT ON COLUMN jobs.job.creator_id IS 'Идентификатор пользователя из ID token фаербейза';
COMMENT ON COLUMN jobs.job.created IS 'Создание';
COMMENT ON COLUMN jobs.job.updated IS 'Последнее обновление';

-- Table Triggers

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON jobs.job
FOR EACH ROW
EXECUTE PROCEDURE jobs.trigger_update_timestamp();