Ticket #15652: system.sortable.sql

/**
 * @param varchar _table The table's name.
 * @param varchar _field Optional. The table field to use. Defaults to name.
 * @return varchar The table's name.
 */
CREATE OR REPLACE FUNCTION sortable(varchar, varchar = 'name')
        RETURNS varchar
AS $$
DECLARE
        _table          alias for $1;
        _field          alias for $2;
BEGIN
        IF      NOT system.column_exists('sort', _table)
        THEN
                EXECUTE $X$
                ALTER TABLE $X$ || quote_ident(_table) || $X$
                ADD COLUMN sort varchar NOT NULL DEFAULT '';
                $X$;
        END IF;
        
        IF      NOT system.constraint_exists(_table || '_sort_check', _table)
        THEN
                EXECUTE $X$
                ALTER TABLE $X$ || quote_ident(_table) || $X$
                ADD CONSTRAINT $X$ || quote_ident(_table || '_sort_check') || $X$
                        CHECK (sort = public.natsort($X$ || quote_ident(_field) || $X$));
                $X$;
        END IF;
        
        IF      NOT system.function_exists(_table || '_sort')
        THEN
                EXECUTE $X$
                CREATE OR REPLACE FUNCTION $X$ || quote_ident(_table || '_sort') || $X$()
                        RETURNS trigger
                AS $DEF$
                BEGIN
                        NEW.sort := public.natsort(NEW.$X$ || quote_ident(_field) || $X$);
                        RETURN NEW;
                END;
                $DEF$ LANGUAGE plpgsql;
                $X$;
        END IF;
        
        IF      NOT system.trigger_exists(_table || '_90_sort_ins', _table)
        THEN
                EXECUTE $X$
                CREATE TRIGGER $X$ || quote_ident(_table || '_90_sort_ins') || $X$
                        BEFORE INSERT ON $X$ || quote_ident(_table) || $X$
                FOR EACH ROW
                WHEN (NEW.$X$ || quote_ident(_field) || $X$ <> '' AND
                        NEW.sort <> public.natsort(NEW.$X$ || quote_ident(_field) || $X$))
                EXECUTE PROCEDURE $X$ || quote_ident(_table || '_sort') || $X$();
                $X$;
        END IF;
        
        IF      NOT system.trigger_exists(_table || '_90_sort_upd', _table)
        THEN
                EXECUTE $X$
                CREATE TRIGGER $X$ || quote_ident(_table || '_90_sort_upd') || $X$
                        BEFORE UPDATE ON $X$ || quote_ident(_table) || $X$
                FOR EACH ROW
                WHEN (OLD.$X$ || quote_ident(_field) || $X$ <> NEW.$X$ || quote_ident(_field) || $X$ AND
                        NEW.sort <> public.natsort(NEW.$X$ || quote_ident(_field) || $X$))
                EXECUTE PROCEDURE $X$ || quote_ident(_table || '_sort') || $X$();
                $X$;
        END IF;
        
        IF      NOT system.index_exists(_table || '_sort_idx', _table)
        THEN
                IF      system.column_exists('status', _table)
                THEN
                        EXECUTE $X$
                        CREATE INDEX $X$ || quote_ident(_table || '_sort_idx') || $X$
                                ON $X$ || quote_ident(_table) || $X$ (status, sort);
                        $X$;
                ELSE
                        EXECUTE $X$
                        CREATE INDEX $X$ || quote_ident(_table || '_sort_idx') || $X$
                                ON $X$ || quote_ident(_table) || $X$ (sort);
                        $X$;
                END IF;
        END IF;
        
        RETURN _table;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION system.sortable(varchar, varchar) IS
'Sortable behavior. Automatically manages a table''s sort field based on another field.';