plpgsql | before update trigger: insert the new pseudo-record in another record and leave the old one as it was

Question

I've got the following table:

CREATE TABLE alumbrado_puntos_luz
(
  id numeric(8,0),
  "GEOMETRY" geometry,
  revision_actual numeric(10,0),
  revision_expirada numeric(10,0),
  CONSTRAINT alumbrado_puntos_luz_id_key UNIQUE (id)
)
WITH (
  OIDS=TRUE
);

When updating the table i would like to update just the revision_expirada value with the next serial and insert the NEW pseudo-record's values in a new record on the same table. I mean, every time i have a change in the drawing (GEOMETRY) i will create a new record with the changes and leave the old one as it is.

I don't know if a trigger is the best way of doing this or creating a rule would be a better idea.

Regards,

Answer 1

I dont which one is better idea rule or trigger but here how it is done using trigger

CREATE TRIGGER updatetable
  INSTEAD OF UDATE
  ON table_name_here
  FOR EACH ROW
  EXECUTE PROCEDURE do_magic();


CREATE OR REPLACE FUNCTION do_magic()
  RETURNS trigger AS
$BODY$

DECLARE
    -- if you need variables they are here
    ta text := 'table_name'
BEGIN 

NEW.serial  :=  nextval('yourtable_sequence'::regclass);
-- OLD.revision_expirada     := NEW.serial

EXECUTE 'UPDATE '|| ta || ' SET revision_expirada = NEW.serial WHERE serial = OLD.serial
EXECUTE 'INSERT INTO ' || ta || ' SELECT  ($1).*' USING NEW;
RETURN NEW;

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION do_magic()
  OWNER TO postgres;

( didn't test it, code edited from my update trigger which inserts new revisions into table instead of updating old records, view has logic to read latest version)

May not be the best way to do it, there may be some "shortcuts" with OLD.* and NEW.* records like if you have default value for serial it may allready contain nextval, causing your serial jumping from 2 .4 .6 etc...

Also add "WITH ( OIDS=FALSE" To your table code) I have impression that using oids is old way to get serial and not recommended for modern database, see http://www.sqlines.com/postgresql/oid , it shared with all tables using it )