it is the code i got from another developer which is written by Postgresql, however i can only use mysql in my environment. does anyone know how to covert the code from Postgresql to Mysql?
Just a disclaimer, I am virtually a newb to sql, I know almost nothing about the code below I don't even know how to create a table. It really makes me feel lost. that's why I need someone to help me transfer the code to mysql so that I can do some experiments on the table
Part1:
1.declare the function of trigger_set_create_time_update_time() whenever i insert data
2.declare the function of trigger_set_update_time() whenever i update data
CREATE OR REPLACE FUNCTION public.trigger_set_create_time_update_time()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
begin
NEW.create_time = NOW() at time zone 'utc' ;
NEW.update_time = NOW() at time zone 'utc' ;
RETURN NEW;
END;
$function$
;
CREATE OR REPLACE FUNCTION public.trigger_set_update_time()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
NEW.update_time = NOW() at time zone 'utc' ;
RETURN NEW;
END;
$function$
;
Part2:
1.Create a table with all the variables and setup a Primary key.
2.Create triggers to call functions when the situation fits.
CREATE TABLE public.good_info (
id varchar NOT NULL,
"name" varchar NULL,
price int8 NOT NULL,
create_time timestamp NOT NULL,
update_time timestamp NOT NULL,
CONSTRAINT good_info_pk PRIMARY KEY (id)
);
create trigger set_create_time_update_time before
insert
on
public.good_info for each row execute function trigger_set_create_time_update_time();
create trigger set_update_time before
update
on
public.good_info for each row execute function trigger_set_update_time();
See Question&Answers more detail:os