table - on delete - how to insert a record into a different table

From: "Campbell, Lance" <lance(at)illinois(dot)edu>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: table - on delete - how to insert a record into a different table
Date: 2016-11-16 23:45:57
Message-ID: B75CD08C73BD3543B97E4EF3964B7D704797A782@CITESMBX1.ad.uillinois.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Postgresql: 9.5

I have two tables xyz and delete_file:

CREATE TABLE xyz
(
id integer NOT NULL DEFAULT nextval(('xyz_id_seq'::text)::regclass),
name character varying DEFAULT ''::character varying,
ext character varying,
created_timestamp timestamp with time zone DEFAULT now(),
CONSTRAINT xyz_pkey PRIMARY KEY (id),
CONSTRAINT xyz_fk_id_fkey FOREIGN KEY (fk_id)
REFERENCES abc (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
OIDS=FALSE
);

CREATE TABLE delete_file
(
id integer NOT NULL DEFAULT nextval(('delete_file_id_seq'::text)::regclass),
file_location text
)
WITH (
OIDS=FALSE
);

When a row is deleted from the table xyz I want to insert a row into the table delete_file using an insert statement similar to this with the values from the row to be deleted.

INSERT INTO delete_file (file_location) (select to_char(created_timestamp,'YYYY')||'/'|| to_char(created_timestamp,'MM') || '/' || to_char(created_timestamp,'DD') || '/' || id || '.' || ext FROM xyz);

What is the best way to do this?

Thanks,

Lance

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Bear Giles 2016-11-16 23:47:42 Re: table - on delete - how to insert a record into a different table
Previous Message Albe Laurenz 2016-11-15 13:13:26 Re: Postgresql 9.5 committing and log sequence number