drop table if exists books;

create table if not exists books 
(id  serial NOT NULL PRIMARY KEY,
 title TEXT NOT NULL,
 data json NULL,
 modified timestamp default now(),
 modified_by text default CURRENT_USER
);

CREATE TRIGGER tr_books_on_change
BEFORE UPDATE 
ON public.books FOR EACH ROW 
EXECUTE PROCEDURE public.trproc_upd_books();

CREATE OR REPLACE FUNCTION public.trproc_upd_books (
)
RETURNS trigger AS
$body$
BEGIN
	NEW.modified:=CURRENT_TIMESTAMP;
	NEW.modified_by:=CURRENT_USER;
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql';

INSERT INTO books (title,data)
VALUES
 ( 'The Tattooed Duke','{ "title": "The Tattooed Duke", "items": {"product": "Diaper","qty": 24}}'::json),
 ( 'She Tempts the Duke','{ "title": "She Tempts the Duke", "items": {"product": "Toy Car","qty": 1}}'::json),
 ( 'The Duke Is Mine','{ "title": "The Duke Is Mine", "items": {"product": "Toy Train","qty": 2}}'::json),
 ( 'What I Did For a Duke','{ "title": "What I Did For a Duke", "items": {"product": "Toy Train","qty": 2}}'::json),
 ( 'King Kong','{ "title": "King Kong", "items": {"product": "Toy Train","qty": 2}}'::json);


select * from books;

update books set title='Петя и Волк' where id=2;