BEGIN;

DROP FUNCTION getringtone(varchar, varchar, varchar, varchar);

CREATE or REPLACE FUNCTION getringtone(integer, integer, integer, varchar) RETURNS VARCHAR AS '
  DECLARE
    ext varchar;
    lid integer;
    nme varchar;
  BEGIN
    select into ext, lid  phonetypecontacttyperingtone.external, phonetypecontacttyperingtone.lookupid
      from phonetypecontacttyperingtone left join contacttype on contacttype.id = phonetypecontacttyperingtone.contacttype 
     where phonetypecontacttyperingtone.company = $1 and phonetype = $3 and contacttype.name = $4;
    IF ext = ''yes'' THEN
       select into nme name from ringtone where id = lid;
       RETURN ''E'' || ''#'' || nme;
    END IF;
    -- ext = no
    select into nme name from phonetyperingtone where id = lid;
    RETURN ''I'' || ''#'' || nme;
  END;
' LANGUAGE plpgsql;

DROP FUNCTION getfastdial(character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION getfastdial(integer, integer, character varying) RETURNS character varying AS '
DECLARE
    _number  varchar;
    _name varchar;
  BEGIN
    SELECT INTO _number, _name number, name FROM fastdial_contact WHERE contact = $1 AND (shortnumber = $3 OR $3 like shortnumber) order by length(shortnumber) desc, number asc limit 1;
    IF FOUND THEN
      RETURN _number || ''#'' || _name;
    END IF;
    SELECT INTO _number, _name number, name FROM fastdial_company WHERE company = $2 AND (shortnumber = $3 OR $3 like shortnumber) order by length(shortnumber) desc, number asc limit 1;
    IF FOUND THEN
      RETURN _number || ''#'' || _name;
    END IF;
    RETURN ''#'';
  END;
' LANGUAGE plpgsql;

COMMIT;
