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.