postgres select basename

FUNCTION basename.sql

CREATE OR REPLACE FUNCTION basename(path text) RETURNS text AS $$

    SELECT reverse((regexp_split_to_array(reverse($1), '/'))[1])

$$
    LANGUAGE SQL IMMUTABLE;

SELECT basename('/Users/username/git/owner/repo');

TRIGGER.sql

CREATE TABLE t(
    path text PRIMARY KEY,
    basename text
);

CREATE OR REPLACE FUNCTION basename(path text) RETURNS text AS $$

    SELECT reverse((regexp_split_to_array(reverse($1), '/'))[1])

$$
    LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION update()
  RETURNS trigger AS
$BODY$
BEGIN
     NEW.basename:=basename(NEW.path);
     RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

DROP TRIGGER IF EXISTS update ON t CASCADE;

CREATE TRIGGER update
    BEFORE INSERT OR UPDATE ON t
    FOR EACH ROW
    EXECUTE PROCEDURE update();

INSERT INTO t(path) VALUES ('path/to/file.txt'), ('path/to/image.png');
SELECT * FROM t;