PostgreSQL URL Table With Computed Values

url_table.sql

-- Drop existing trigger and function if they exist
DROP TRIGGER IF EXISTS trigger_before_insert_update_url_entry ON url_entries;
DROP FUNCTION IF EXISTS before_insert_url_entry();

-- Drop the existing table if it exists
DROP TABLE IF EXISTS url_entries;

-- Create the new table
CREATE TABLE url_entries (
    id SERIAL PRIMARY KEY,
    href TEXT NOT NULL,
    name VARCHAR(255),
    protocol TEXT,
    username TEXT,
    password TEXT,
    host TEXT,
    port TEXT,
    pathname TEXT,
    search TEXT,
    hash TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create the function for parsing URLs
CREATE OR REPLACE FUNCTION before_insert_url_entry() RETURNS TRIGGER AS $$
DECLARE
  v_regex_pattern CONSTANT text := '^([a-zA-Z][a-zA-Z0-9+\-.]*:)//(([^:/?#]+)(:([^:/?#]*))?@)?([^:/?#]+)(:(\d+))?(\/[^?#]*)?(\?[^#]*)?(#.*)?$';
  v_matches text[];
BEGIN
    v_matches := regexp_matches(NEW.href, v_regex_pattern);

    IF v_matches IS NOT NULL THEN
        NEW.protocol := v_matches[1];
        NEW.username := v_matches[3];
        NEW.password := v_matches[5];
        NEW.host := v_matches[6];
        NEW.port := v_matches[8];
        NEW.pathname := v_matches[9];
        NEW.search := v_matches[10];
        NEW.hash := v_matches[11];
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create the trigger
CREATE TRIGGER trigger_before_insert_update_url_entry
BEFORE INSERT OR UPDATE ON url_entries
FOR EACH ROW EXECUTE FUNCTION before_insert_url_entry();