15
« on: January 20, 2020, 03:01:15 PM »
Hi Monica!
Unfortunately, in your example, there is no routing, and especially the functionality of "alternatives".
Over the weekend, I made a working function in postgres, but it turned out so ugly!
I’ll leave this function here, maybe a professional will see it, will be horrified and advise how to ennoble it.
CREATE FUNCTION public.get_route(_caller text, _called text, _addres text) RETURNS SETOF record
LANGUAGE plpgsql
AS $$DECLARE
_location text :='-';
_tmp text;
_sum int;
_sumAlt int;
_sumCld int;
_result RECORD;
_record RECORD;
/* max count of calls is 5*/
_callto1 text;
_callto2 text;
_callto3 text;
_callto4 text;
_callto5 text;
BEGIN
SELECT COUNT(id)
FROM ( SELECT id
FROM users
WHERE username=_called
AND location IS NOT NULL)
AS sub
INTO _sumCld;
IF _sumCld IS NULL THEN
_sumCld = 0;
END IF;
SELECT COUNT(id)
FROM ( SELECT id
FROM users
WHERE alternatives ~ _called
AND location IS NOT NULL)
AS sub
INTO _sumAlt;
IF _sumAlt IS NULL THEN
_sumCld = 0;
END IF;
_sum = _sumAlt + _sumCld;
IF _sum > 1 THEN
_location ='fork';
IF _sumAlt > 1 THEN
FOR _record IN
SELECT location
FROM users
WHERE alternatives ~ _called
AND location IS NOT NULL
LOOP
CASE _sumAlt
WHEN 1 THEN _callto1 = _record.location;
WHEN 2 THEN _callto2 = _record.location;
WHEN 3 THEN _callto3 = _record.location;
WHEN 4 THEN _callto4 = _record.location;
WHEN 5 THEN _callto5 = _record.location;
END CASE;
_sumAlt = _sumAlt -1;
EXIT WHEN _sumAlt = 0;
END LOOP;
IF _sumCld = 1 THEN
SELECT location
FROM users
WHERE username=_called
AND location IS NOT NULL
INTO _tmp;
CASE _sum
WHEN 1 THEN _callto1 = _tmp;
WHEN 2 THEN _callto2 = _tmp;
WHEN 3 THEN _callto3 = _tmp;
WHEN 4 THEN _callto4 = _tmp;
WHEN 5 THEN _callto5 = _tmp;
END CASE;
END IF;
ELSE
SELECT location
FROM users
WHERE username=_called
AND location IS NOT NULL
INTO _callto1;
SELECT location
FROM users
WHERE alternatives ~ _called
AND location IS NOT NULL
INTO _callto2;
END IF;
ELSE
IF _sumCld = 1 THEN
SELECT location
FROM users
WHERE username=_called
AND location IS NOT NULL
INTO _location;
ELSEIF _sumAlt = 1 THEN
SELECT location
FROM users
WHERE alternatives ~ _called
AND location IS NOT NULL
INTO _location;
ELSE
RETURN;
END IF;
END IF;
SELECT _location,_callto1,_callto2,_callto3,_callto4,_callto5
INTO _result;
RETURN NEXT _result;
RETURN;
END;
$$;
Table `users` based on your examples, but slightly modified.
CREATE TABLE public.users (
username text NOT NULL,
password text,
vcard text,
first_name text,
last_name text,
address text,
inuse integer,
location text,
expires timestamp without time zone,
alternatives text,
realm text,
reginterval integer DEFAULT 300,
lnr boolean DEFAULT false,
context text,
id bigint DEFAULT nextval('public.user_id_seq'::regclass) NOT NULL,
record text,
skype text,
mobile text,
groupe text
);
And part of register.conf:
[call.route]
query=SELECT * FROM get_route('${caller}','${called}','${address}') AS t(location text,"callto.1" text,"callto.2" text,"callto.3" text,"callto.4" text,"callto.5" text)
result=location
priority=110
Show Posts
