string is sometimes null ?

[Graeme Hinchliffe]

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,curren t_start,last_update,m''||monthcurr||''_in AS monthin,m''||monthcurr||''_out AS 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 : [phone]

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Stephan Szabo]

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,curren t_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.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Graeme Hinchliffe]

> 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 : [phone]

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Holger Klawitter]

-----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)

iD8DBQFBW9CJ1Xdt0HKSwgYRAjg9AJoCA8rZYcDxsdey1neJNj ysnPByagCfUIeL
CJT69fhzoIY+RG8btBrPnCA=
=YsB2
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Stephan Szabo]

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...

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster