Cascading Soft Delete (stackoverflow answer)

https://stackoverflow.com/a/78694335/4188138

You must execute the code again to save those values in the database.

-- INIT database

-- main table, will contain non deleted data
CREATE TABLE public.active_items (
    id SERIAL PRIMARY KEY,
    name TEXT
);

-- the archive table, inheriting columns and column types from the main table. Will only contain deleted data.
CREATE TABLE public.archived_items (
    deleted_at TIMESTAMP,
    deleted_by INTEGER
) INHERITS (public.active_items);

INSERT INTO public.active_items (name) VALUES
    ('Item 1'),
    ('Item 2');

-- Create am updateable view for non-deleted items since by `SELECT * FROM public.active_items;` will include
-- data from public.archived_items since it's an inherited table
CREATE OR REPLACE VIEW public.active_items_view AS
SELECT * FROM ONLY public.active_items;

SELECT * from public.active_items;
-- returns:
-- id,name
-- 1,Item 1
-- 2,Item 2

-- Update items through the view
UPDATE public.active_items_view
SET name = name || ' - updated';

SELECT * from public.active_items_view;
-- returns:
-- id,name
-- 1,Item 1 - updated
-- 2,Item 2 - updated

-- Insert a new item through the view
INSERT INTO public.active_items_view (name)
VALUES ('Item inserted via view');

SELECT * from public.active_items_view;
-- returns:
-- id,name
-- 1,Item 1 - updated
-- 2,Item 2 - updated
-- 3,Item inserted via view

-- placeholder function to track who deleted the record
CREATE OR REPLACE FUNCTION public.get_current_user_id()
RETURNS INTEGER AS $$
BEGIN
    RETURN 1; -- Replace with logic to retrieve actual user id
END;
$$ LANGUAGE plpgsql;

-- Create a function to move deleted items to the archive table
CREATE OR REPLACE FUNCTION public.move_to_archive()
RETURNS TRIGGER AS $$
BEGIN
    -- Move deleted rows to the archive table
    INSERT INTO public.archived_items
    SELECT *,
           CURRENT_TIMESTAMP,
           public.get_current_user_id()
    FROM OLD_TABLE;

RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Create a trigger to automatically archive deleted items
CREATE TRIGGER soft_delete_trigger
AFTER DELETE ON public.active_items
REFERENCING OLD TABLE AS OLD_TABLE
FOR EACH STATEMENT EXECUTE FUNCTION public.move_to_archive();

-- currently contains nothing
SELECT * FROM public.archived_items;

-- This will trigger the function above to move the data to the archive table
DELETE FROM public.active_items_view;

SELECT * FROM public.archived_items;
-- returns:
-- id, name,                   deleted_at,                  deleted_by,
-- 1,  Item 1 - updated,       2024-07-01 21:33:26.967218,  1
-- 2,  Item 2 - updated,       2024-07-01 21:33:26.967218,  1
-- 3,  Item inserted via view, 2024-07-01 21:33:26.967218,  1

-- contains nothing
SELECT * FROM public.active_items_view;

id	name
1	Item 1
2	Item 2

id	name
1	Item 1 - updated
2	Item 2 - updated

id	name
1	Item 1 - updated
2	Item 2 - updated
3	Item inserted via view

id	name	deleted_at	deleted_by

id	name	deleted_at	deleted_by
1	Item 1 - updated	2024-07-01 21:55:27.440011	1
2	Item 2 - updated	2024-07-01 21:55:27.440011	1
3	Item inserted via view	2024-07-01 21:55:27.440011	1

id	name

CREATE TABLE
CREATE TABLE
INSERT 0 2
CREATE VIEW

UPDATE 2

INSERT 0 1

CREATE FUNCTION
CREATE FUNCTION
CREATE TRIGGER

DELETE 3