[GENERAL] freeradius postgresql sql query glitch

[Josip Rodin]

On Mon, Dec 07, 2009 at 11:53:45PM +0100, Josip Rodin wrote:
> On Mon, Dec 07, 2009 at 10:02:39PM +0000, Adrian Klaver wrote:
> > > Mon Dec  7 13:19:01 2009 : Error: [ourlittle_sql] Couldn't update SQL
> > > accounting STOP record - ERROR:  invalid input syntax for integer: ""
> > > 
> > > accounting_stop_query = "UPDATE ${acct_table2} \
> > >   SET
> > >   AcctSessionTime = CASE WHEN '%{Acct-Session-Time}' = '' THEN \      
> > >   (EXTRACT(EPOCH FROM ('%S'::TIMESTAMP WITH TIME ZONE -
> > > AcctStartTime::TIMESTAMP WITH TIME ZONE \
> > >   - '%{%{Acct-Delay-Time}:-0}'::INTERVAL)))::BIGINT ELSE
> > > '%{Acct-Session-Time}' END, \           
> > > 
> > > I'm not sure what to do... can the query be rewritten in a manner that
> > > would
> > > allow for both use cases?
> > 
> > If I understand correctly the below may work. If %{Acct-Session-Time} is
> > an empty string it will return NULL otherwise it will return
> > %{Acct-Session-Time}.
> > 
> >  ELSE
> > NULLIF('%{Acct-Session-Time}','') END,
> 
> Thanks, that should work, with a slight modification - explicit cast to
> 'bigint', because a nullif()'ed '' is still a 'text' by default.
> 
> The two cases then evaluate like this:
> 
> pgsql=# select CASE WHEN '' = '' THEN 1234::BIGINT ELSE NULLIF('', '')::BIGINT END AS value;
>  value
> -------
>   1234
> (1 row)
> 
> pgsql=# select CASE WHEN '13' = '' THEN 1234::BIGINT ELSE NULLIF('13', '')::BIGINT END AS value;
>  value
> -------
>     13
> (1 row)
> 

Alan, please apply the patch:

--- raddb/sql/postgresql/dialup.conf~ 2009-12-08 00:06:36.000000000 +0100
+++ raddb/sql/postgresql/dialup.conf  2009-12-08 00:06:36.000000000 +0100
@@ -226,7 +226,8 @@
   SET AcctStopTime = ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), \
   AcctSessionTime = CASE WHEN '%{Acct-Session-Time}' = '' THEN \
   (EXTRACT(EPOCH FROM ('%S'::TIMESTAMP WITH TIME ZONE - AcctStartTime::TIMESTAMP WITH TIME ZONE \
-  - '%{%{Acct-Delay-Time}:-0}'::INTERVAL)))::BIGINT ELSE '%{Acct-Session-Time}' END, \
+  - '%{%{Acct-Delay-Time}:-0}'::INTERVAL)))::BIGINT \
+  ELSE NULLIF('%{Acct-Session-Time}','')::BIGINT END, \
   AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input-Octets}:-0}'::bigint), \
   AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Output-Octets}:-0}'::bigint), \
   AcctTerminateCause = '%{Acct-Terminate-Cause}', \

-- 
     2. That which causes joy or happiness.