maya
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
ot_ro_get_device_conf(character varying, boolean)
Parameters
Name
Type
Mode
serialnumber
character varying
IN
descriptive
boolean
IN
Definition
WITH q as (select dc.iddeviceconf,dc.configname,dc.defaultval,cd.configval client_configval, ddc.configval dev_configval FROM deviceconf dc LEFT JOIN device d ON d.idmodeldevice = dc.idmodeldevice LEFT JOIN device_deviceconf ddc ON ddc.iddevice = d.iddevice and ddc.iddeviceconf = dc.iddeviceconf LEFT JOIN technicalaccount t ON t.idtechnicalaccount = d.idtechnicalaccount LEFT JOIN client_deviceconf cd ON cd.idclient = t.idclient AND dc.iddeviceconf = cd.iddeviceconf WHERE d.serialnumber = $1 order by 1), q1 as (SELECT iddeviceconf,configname,defaultval,client_configval, dev_configval, case when length(dev_configval)>0 then dev_configval WHEN length(client_configval)> 0 THEN client_configval ELSE defaultval END effective_val , case when length(dev_configval)>0 then 'device_deviceconf' WHEN length(client_configval)> 0 THEN 'client_deviceconf' ELSE 'deviceconf' END src_val FROM q), q2 as (SELECT string_agg(configname||'=>'||coalesce(effective_val,'null'),',') res , string_agg('src_'||configname||'=>'||coalesce(src_val,'null'),',') res1 FROM q1) select hstore(res)||hstore(res1) from q2 ;