/**
 * @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.';