CREATE OR REPLACE FUNCTION "drugreferencefull"."get_generics" (integer) RETURNS text AS
$body$
declare
    _mancode alias for $1;
    _generics text;
    _row record;
begin
     _generics :='';
     for _row in
      SELECT
            GMMAN.mancode,
            GENMAN.generic
            FROM
                          drugreferencefull.gmman GMMAN
            INNER JOIN    drugreferencefull.genman GENMAN
            ON            (GMMAN.gencode = GENMAN.gencode)
            WHERE
                         GMMAN.mancode = _mancode
      loop
          _generics := _generics ||  coalesce(_row.generic, '')||', ';
     end loop;

	return _generics;
end
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

COMMENT ON FUNCTION "drugreferencefull"."get_generics"(integer)
IS 'gets all generics for a particular drug in mims annual, returned as a comma separated string.';