Обсуждение: string is sometimes null ?

Hiya
    I have written a PL/PGSQL function which tots up users accounting
information from their RADACCT data on the fly.  So each insert or
update modifies their totals.

    This has worked without error when I have tested it by hand with hand
crafted inserts and updates, however when I put it on the actual live
accounting server (both systems are running 7.4.3) it seems to work
partially.

    The RADIUS server is reporting problems when trying to write START of
accounting entries, and the error given is:

ERROR:  cannot EXECUTE a null querystring CONTEXT:  PL/pgSQL function
"radacct_trig" line 43 at open

The lines its refering to are:

--- START ---

str:=''SELECT
username,year_of_jan,current_in,current_out,current_start,last_update,m''||monthcurr||''_in AS
monthin,m''||monthcurr||''_outAS monthout 
  FROM customer_stats WHERE username=''''''||username||'''''''';

  OPEN curs FOR EXECUTE str;

--- END ---

str is defined as a varchar(255).  What could be causing str to be NULL
when the execute is caused?  I would have thought if some of the
variables were null that it would have complained about a malformed SQL
query but this is saying the string is NULL!

any ideas help much appreciated, thanks.

--
-----
Graeme Hinchliffe (BSc)
Core Internet Systems Designer
Zen Internet (http://www.zen.co.uk/)

Direct: 0845 058 9074
Main  : 0845 058 9000
Fax   : 0845 058 9005

On Wed, 29 Sep 2004, Graeme Hinchliffe wrote:

>     The RADIUS server is reporting problems when trying to write START of
> accounting entries, and the error given is:
>
> ERROR:  cannot EXECUTE a null querystring CONTEXT:  PL/pgSQL function
> "radacct_trig" line 43 at open
>
> The lines its refering to are:
>
> --- START ---
>
> str:=''SELECT
> username,year_of_jan,current_in,current_out,current_start,last_update,m''||monthcurr||''_in
> AS monthin,m''||monthcurr||''_out AS monthout
>   FROM customer_stats WHERE username=''''''||username||'''''''';

If monthcurr or username are null, the above will be null.
Anything || NULL => NULL.

> If monthcurr or username are null, the above will be null.
> Anything || NULL => NULL.

AH! thanks sorted it now.  That is not the behaviour I would have
expected :)

Is there a concatination operator that will not do this? IE

if

var1 || var2 || var3

and var2 is null would result in just var1var3  ?

Many thanks for your help

--
-----
Graeme Hinchliffe (BSc)
Core Internet Systems Designer
Zen Internet (http://www.zen.co.uk/)

Direct: 0845 058 9074
Main  : 0845 058 9000
Fax   : 0845 058 9005

Graeme Hinchliffe wrote:
>>If monthcurr or username are null, the above will be null.
>>Anything || NULL => NULL.
>
> AH! thanks sorted it now.  That is not the behaviour I would have
> expected :)
>
> Is there a concatination operator that will not do this? IE
> if
> var1 || var2 || var3
>
> and var2 is null would result in just var1var3  ?

You could use coalesce(var1,'') || coalesce(var2,'')
Or, wrap that in your own function and create your own operator.

--
   Richard Huxton
   Archonet Ltd

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

> Is there a concatination operator that will not do this? IE
> var1 || var2 || var3
> and var2 is null would result in just var1var3  ?

You have to coalesce your vars before concatenating, as in

  coalesce(var1,'') || coalesce(var2,'') || coalesce(var3,'')

Mit freundlichem Gruß / With kind regards
    Holger Klawitter
- --
lists <at> klawitter <dot> de
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQFBW9CJ1Xdt0HKSwgYRAjg9AJoCA8rZYcDxsdey1neJNjysnPByagCfUIeL
CJT69fhzoIY+RG8btBrPnCA=
=YsB2
-----END PGP SIGNATURE-----

On Thu, 30 Sep 2004, Graeme Hinchliffe wrote:

> > If monthcurr or username are null, the above will be null.
> > Anything || NULL => NULL.
>
> AH! thanks sorted it now.  That is not the behaviour I would have
> expected :)
>
> Is there a concatination operator that will not do this? IE
>
> if
>
> var1 || var2 || var3
>
> and var2 is null would result in just var1var3  ?

You can use coalesce as suggested... However, since you are building a
statement, think about what you want to do when either of those are NULL.
You might want to use an empty string, or perhaps instead of ='' you might
actually be wanting an IS NULL clause, etc...