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.
 
2. Apply to yate this patch https://github.com/vir/yate/commit/0fa7f90f87e76ff67ea7fb83433a18ae489f687e
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