[ previous ] [ next ] [ threads ]
 To :  Ganapathi Chidambaram <ganapathi.rj@g...>,Yate mailing list <yate@v...>
 From :  aucyxob <aucyxob@y...>
 Subject :  Re: [yate] [ YATE 6.0 ] Routing call using PgSQL Stored function
 Date :  Sun, 01 Oct 2017 10:29:27 +0300
Hello Ganapathi Chidambaram.
 
Your answer from the database should consist of one line and many columns with corresponding to parameters names. So your pgsql function should return something like this:
| location | callto.1 | dtmfpass | pbxassist | tonedetect_out | callto.1.secure | copyparams |
------------------------------------------------------------------------------------------------------------------------
| fork | sip/sip:.. | false | true | true | yes | pbxassist,dt..|
This is not very convenient if you need to return a different number of parameters each time.
If you want to return a result from the database as a key-value set
| key | value |
------------------------
| location | fork |
| callto.1 | sip/sip |
| dtmfpas | false |
....
you should consider the following solutions:
 
1. You can use extmodule or javascript module as proxy for database which will convert your resultset's columns key-value to yate's message param-value.
 
or use following yate's very useful fork https://github.com/vir/yate/wiki/MyChanges
 
register.conf
 
[call.route]
accountÞfault
query=SELECT * FROM route_master('${billid}','${called}','${trace}');
result=location
priority0
namevaluepairs=yes
 
PostgreSQL
 
CREATE OR REPLACE FUNCTION route_master(
    IN billid text,
    IN caller text,
    IN called text,
  )
  RETURNS TABLE(key text, value text) AS
$BODY$
DECLARE
    -- variables
BEGIN
-- do something
-- append lines to resultset
    key := 'location'; value := 'fork'; RETURN NEXT;
    key := 'copyparams'; value := 'pbxassist,dtmfpass'; RETURN NEXT;
 
    RETURN;
 
END;
$BODY$
  LANGUAGE plpgsql;
 
30.09.2017, 15:43, "Ganapathi Chidambaram" <ganapathi.rj@g...>:
Hi
 
As i used pgsql stored function to route a call to user/group. Whereas i am getting result properly but even though calls are not routing to the user .
 
find the below mentioned configuration for the same.
 
register.conf
 
query=SELECT * FROM route_master('${billid}','${called}','${trace}');
result=location
 
Output of PgSQL:
 
field value
trace 4444407
callto.1 sip/sip:4444407@1...:5060
dtmfpass false
location fork
pbxassist true
copyparams pbxassist,dtmfpass
tonedetect_out true
callto.1.secure yes
 
Logs :
 
<pgsqldb:ALL> Query for 'db.1' returned 8 rows, 8 affected [0xcbd510]
Returned true 'database' delay=0.230988
  thread=0x7f6e54002c10 'Call Router'
  data=0x7f6e4c006200
  retval='(null)'
  param['account'] = 'technobureau_db'
  param['query'] = 'SELECT * FROM route_master('1506767265-8','4444407','');'
  param['results'] = 'true'
  param['handlers'] = 'pgsqldb:100'
  param['columns'] = '2'
  param['rows'] = '8'
  param['affected'] = '8'
  param['dbtype'] = 'pgsqldb'
2017-09-30_17:34:43.806147 <register:WARN> Skipping void route #2
2017-09-30_17:34:43.806158 <register:WARN> Skipping void route #3
2017-09-30_17:34:43.806172 <register:WARN> Skipping void route #4
2017-09-30_17:34:43.806182 <register:WARN> Skipping void route #5
2017-09-30_17:34:43.806192 <register:WARN> Skipping void route #6
2017-09-30_17:34:43.806202 <register:WARN> Skipping void route #7
2017-09-30_17:34:43.806211 <register:WARN> Skipping void route #8
 
Call.cdr :
 
Status :hangup
Reason : Offline
 
But actually user logged-in.
 
Kindly help me out.

Regards,
Ganapathi