From: | Josip Rodin <joy(at)entuzijast(dot)net> |
---|---|
To: | freeradius-users(at)lists(dot)freeradius(dot)org, pgsql-general(at)postgresql(dot)org |
Subject: | Re: freeradius postgresql sql query glitch |
Date: | 2009-12-07 22:53:45 |
Message-ID: | 20091207225345.GA4594@orion.carnet.hr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
--
2. That which causes joy or happiness.
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2009-12-07 23:33:11 | Re: how to ensure a client waits for a previous transaction to finish? |
Previous Message | Dan Kortschak | 2009-12-07 22:32:15 | Re: how to ensure a client waits for a previous transaction to finish? |