CREATE OR REPLACE FUNCTION public."mmgb_getAllBooks"(
    IN parm_moneymgr character varying,
    IN parm_sortorder character varying DEFAULT 'ASC'::character varying)
  RETURNS TABLE(c1 bigint, c2 character varying, c3 character varying, c4 character varying, c5 character varying, c6 real, c7 timestamp without time zone) AS
$BODY$DECLARE
    mmgr_flag boolean := NULL;
    asc_flag boolean := true;
BEGIN
    mmgr_flag = (parm_moneymgr IS NOT NULL);
    asc_flag = (NOT parm_sortorder = 'DESC');
    RETURN QUERY
	SELECT b.mmgb_id,
	   i.instt_abbr,
	   i.instt_shortname,
	   c.cur_iso,
	   b.mmgb_shortname,
	   b.mmgb_weight,
	   b.mmgb_infodate
	FROM mmgr_book b,
		public."InstrumentType" i,
		currency c,
		public."MoneyMgr" m,
		mmgr_status s
	WHERE b.mmgb_insttid = i.instt_id
	AND (m.mmgr_shortname = parm_moneymgr OR NOT mmgr_flag)
	AND b.mmgb_curid = c.cur_id
	AND s.mmgs_id = m.mmgr_statid
	AND s.mmgs_shortname = 'Active'
	AND m.mmgr_id = b.mmgb_mmgrid

	ORDER BY asc_flag,
	CASE WHEN asc_flag THEN
	    b.mmgb_infodate
	END ASC,
	CASE WHEN NOT asc_flag THEN
	    b.mmgb_infodate
	END DESC;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION public."mmgb_getAllBooks"(character varying, character varying)
  OWNER TO mydbo;
COMMENT ON FUNCTION public."mmgb_getAllBooks"(character varying, character varying) IS 'optional filters to cater for known use cases';