Soft delete with unique index and undo delete postgresql

soft-delete-with-undo-postgresql.sql

CREATE TABLE IF NOT EXISTS artices (
	id serial PRIMARY KEY,
	title VARCHAR (50) NOT NULL,
	slug VARCHAR (255) NOT NULL,
    deleted_at  TIMESTAMP NULL,    
	created_on TIMESTAMP not NULL DEFAULT NOW()
);


CREATE UNIQUE INDEX IF NOT EXISTS unique_slug
ON artices (slug) WHERE deleted_at IS NULL;


CREATE OR REPLACE
FUNCTION maintain_slug_uniqueness() RETURNS TRIGGER AS 
$BODY$
DECLARE 
	not_deleted_count INT;
BEGIN 
    not_deleted_count := (
            SELECT
	            count(artices.slug)
            FROM
	            artices
            WHERE
	            artices.slug = NEW.slug 
	            	AND 
	            deleted_at IS NULL
	            	AND
	            artices.id <> NEW.id
    );

    IF not_deleted_count >= 1 THEN
        NEW.slug := NEW.slug || '-' || md5(random()::text);
    END IF;


RETURN NEW;
END;

$BODY$
LANGUAGE plpgsql;



CREATE TRIGGER maintain_slug_uniqueness
  BEFORE UPDATE
  ON artices
  FOR EACH ROW
  EXECUTE PROCEDURE maintain_slug_uniqueness();




INSERT
	INTO
	artices 
	("title", "slug")
VALUES (
	'rowadz',
	'soft-delete-unique'
);