Create a table, and add a stored procedure to it. Add a stored procedure so that every time a record is updated, the updated at variable is automatically set to the current time.

Question

Create a table, and add a stored procedure to it. Add a stored procedure so that every time a record is updated, the updated at variable is automatically set to the current time.

Code for creating table:

CREATE TABLE key value (

id SERIAL,

key VARCHAR(128) UNIQUE,

value VARCHAR(128) UNIQUE,

created at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

updated at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

PRIMARY KEY(id)

);

Summary

There are four steps in this process:

1. Create a stored procedure called set key timestamp that will be called whenever a new record is added to the table.

2. The table is then formed with the create table command.

3. Then, each time a record is added to the table, a trigger named set timestamp is created, which calls the method set key timestamp.

4. Finally, enter the rows into the table to verify the codes.

Explanation

There are three steps to creating the desired table ‘key value’ and the stored procedure:

1) Write a stored procedure that adds the current timestamp to each record when it is added.

CREATE OR REPLACE FUNCTION IN CODE set key timestamp()
RETURNS TRIGGER AS $$

BEGIN

NEW. Updated at = NOW();

New. Created at=NOW();

RETURN NEW; END;

$$ LANGUAGE NEW. Updated at = NOW();

NEW. Created at=NOW();

RETURN NEW;

END;

$$ LANGUAGE NEW. Updated at = NOW();

Newcreated at=NOW plpgsql;

2) Make the table you want:

CREATE TABLE key value

( id SERIAL, key VARCHAR(128) UNIQUE, value VARCHAR(128) UNIQUE, created at TIMESTAMP NOT NULL DEFAULT NOW(), updated at TIMESTAMP NOT NULL DEFAULT NOW(), PRIMARY KEY(id) );

CREATE TABLE key value ( id SERIAL, key VARCHAR(128) UNIQUE, value)

3)Then, create the trigger named ste_timestamp:

CREATE TRIGGER ste_timestamp

BEFORE UPDATE ON midtable

FOR EACH ROW

EXECUTE PROCEDURE set_key_timestamp();

4) Finally, insert the records into the table and double-check the table’s created at and updated at characteristics.

Also read, a program for restaurant maintain breakfast billing system