How to issue a DML with WHERE clause comparing a value which is trigger generated in table?

Question

Suppose I have a table with CREATE,

CREATE TABLE TEST (
 year int,
 month int, 
 date int, 
 hr int, 
 min int, 
 sec int, 
 timestamp timestamp,
 value double
);
CREATE FUNCTION timestamp_insert() RETURNS trigger
LANGUAGE plpgsql
AS $$
 DECLARE datestr TEXT ;
 DECLARE timestr TEXT ;
 begin
 datestr := new.year || '-' || new.month || '-' || new.date || ' ' || new.hour || ':' || new.min || ':' || new.sec;+ 
 new.timestamp := datestr :: TIMESTAMP 
 return new;
 end;
$$
CREATE TRIGGER timestamp_insert BEFORE INSERT OR UPDATE ON TEST FOR EACH ROW EXECUTE PROCEDURE timestamp_insert();

Now I want to update a certain row based on the timestamp currently on the table and that from a new set of data. Something like,

UPDATE TEST SET value = ? WHERE timestamp < "generated timestamp from a new set of (year, month, date, ...)"

Is it possible to do something like this in SQL or do I need to programmatically genrate the timestamp and then simply pass that value in SQL?

• user1822: Why don't you just have only the timestamp column?
• Vineet Menon: The data with which I'm populating the rows doesn't have ts field, only the bits and pieces.

Answer 1

There is a function named make_timestamp() that you can use for this. It also makes your trigger function a lot shorter:

CREATE FUNCTION timestamp_insert() 
 RETURNS trigger
 LANGUAGE plpgsql
AS 
$$
begin
 new.timestamp := make_timestamp(new.year, new.month, new.date, new.hour, new.min, new.sec);
 return new;
end;
$$

The same function can be used in the UPDATE as well

UPDATE TEST 
 SET value = ? 
WHERE timestamp < make_timestamp(year, month, date, hour, min, sec);

---

If you are using Postgres 12, you don't need the trigger as it now supports generated/calculated columns:

CREATE TABLE TEST 
(
 year int,
 month int, 
 date int, 
 hr int, 
 min int, 
 sec int, 
 ts timestamp generated always as (make_timestamp(year, month, date, hr, min, sec)) stored,
 value double precision
);