creating triggers and functions in postgresql

Question

how can i create a trigger function before adding/updating,the function should check records that have the same id (i.e comparison by id with existing objects that have the same property as the temporary_object)If a record with the id is found, then that entry is set to the time_dead, and then it adds an entry containing the corresponding values of the attributes found in that record (except those that are set for a new record), when time_dead is empty then time_create of a new time is equal to that time at the current moment . Thus,a new record time_create is like the time_dead's ancestor. If a record with that id is found then it is added to the database with the establishment as the time_create of the current time.

for example here is a simple explanation(just for explanation purposes)

id time_create   time-dead  student  amount

 1  06.12           07.12    henry   500

 1  07.12                    henry   1000

so if a student called henry with id 1 entered a room at 06.12 and left at 07.12 the next time he enters another room again time_dead will be equal to time_create(so time_dead of old entry and time_create of new entry - will be equal) these are my tables below in sql format

CREATE TABLE temporary_object
(
  id integer NOT NULL,
  time_create timestamp without time zone NOT NULL,
  time_dead timestamp without time zone,
  CONSTRAINT temporary_object_pkey PRIMARY KEY (id, time_create)
)

CREATE TABLE persons
(
  fname text,
  fsurname text,
)
INHERITS (temporary_object)


CREATE TABLE rooms
(
  roomnum integer,
  course integer,
  passport text,
  students_number text
)
INHERITS (temporary_object)

this is what i am trying to do but im afraid i do not know how to finish it but im 100% not right may some help out

CREATE TRIGGER trigger2
 BEFORE INSERT OR UPDATE
 ON persons
 FOR EACH ROW
 EXECUTE PROCEDURE func1();

and this is the function

 CREATE OR REPLACE FUNCTION func1() RETURNS TRIGGER AS $persons$
 DECLARE
        time_create          integer;
        time_dead     timestamp;
        id       timestamp;
    BEGIN

 IF (TG_OP = 'INSERT') THEN

            time_create=

András Váczi · Accepted Answer · 2012-12-06 22:12:52Z

6

I can't tell you what I'm missing from your question, but I try to answer what I think I understood.

Row level triggers can access the version of the row affected with the NEW and OLD variables (depending on TG_OP). In this case, you can use NEW:

CREATE OR REPLACE FUNCTION func1() 
RETURNS TRIGGER AS 
$persons$
DECLARE
    i integer;
BEGIN
    IF TG_OP = 'INSERT'
    THEN
        UPDATE persons 
        SET time_dead = NEW.time_create
        WHERE 
            id = NEW.id -- we are looking for the same ID
            AND time_dead IS NULL
        ;
    ELSE -- UPDATE
        -- do here something
    END IF;
END;
$persons$
LANGUAGE plpgsql;

This is only a starter, modify it to your needs.

answered Dec 6, 2012 at 22:12

András Váczi

3,02225 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

creating triggers and functions in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related