Обсуждение: LOOping clobbering variable

Hi folks,

The enclosed function returns a proper result only when the LOOP and its enclosed functionality is removed from the
function.Notice near the return I have  xml=xml||'</transaction>'; 

That should at the least return what ever is in xml with </transaction> concatinated, or so I would hope. But it's
empty.

I am using postgresql 7.4.6 on FreeBSD.

Any assistance in correcting this would be greatly appreciated.

TIA

CREATE OR REPLACE FUNCTION public.fetch_order(int8)
  RETURNS text AS
'Declare
 transcode ALIAS for $1;
 data RECORD;
 tmp_data RECORD;
 tmp_data2 RECORD;
 xml TEXT;
 tmp_xml TEXT;
 tmp_item_name TEXT;
Begin
 xml=\'<transaction>\';
 select * into data from orders where id=transcode::int8;
 IF data.type = \'so\' THEN
  select * into tmp_data from customers where id = data.cus_ven_id;
  xml=xml||\'<customer>\';
  xml=xml||\'<name>\'||tmp_data.customer||\'</name>\';
  xml=xml||\'<address>\'||tmp_data.address||\'</address>\';
  xml=xml||\'<phone_number>\'||tmp_data.phone_number||\'</phone_number>\';
  xml=xml||\'<fax_number>\'||tmp_data.fax_number||\'</fax_number>\';
  xml=xml||\'<e_mail>\'||tmp_data.e_mail||\'</e_mail>\';
  xml=xml||\'</customer>\';
 END IF;
 FOR tmp_data IN SELECT * FROM order_line_item where order_number=transcode::int8 LOOP
  select * into tmp_data2 from items where id=tmp_data.item_id;
  xml=xml||\'<item>\'||tmp_data2.item;
  xml=xml||\'<decription>\'||tmp_data2.description||\'</description>\';
  xml=xml||\'<qty>\'||tmp_data.quantity||\'</qty>\';
  xml=xml||\'<cost>\'||tmp_data.cost||\'</coast>\';
  xml=xml||\'</item>\';
 END LOOP;
 xml=xml||\'</transaction>\';
 return xml;
End;'
  LANGUAGE 'plpgsql' VOLATILE;

--
_______________________________________________
Get your free email from http://www.kittymail.com

Powered by Outblaze

On Sat, Jan 01, 2005 at 07:38:16PM -0800, K Anderson wrote:

> The enclosed function returns a proper result only when the LOOP
> and its enclosed functionality is removed from the function. Notice
> near the return I have  xml=xml||'</transaction>';
>
> That should at the least return what ever is in xml with
> </transaction> concatinated, or so I would hope. But it's empty.

I'd guess that one of the values you're concatenating is NULL.
Concatenating NULL with anything results in NULL, so if you assign
that back to xml then all subsequent concatenations will also result
in NULL.

You can use COALESCE to convert NULLs to empty strings.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Sat, 1 Jan 2005, K Anderson wrote:

> The enclosed function returns a proper result only when the LOOP and its
> enclosed functionality is removed from the function. Notice near the
> return I have xml=xml||'</transaction>';
>
> That should at the least return what ever is in xml with </transaction>
> concatinated, or so I would hope. But it's empty.

Is it possible that you're ending up with a NULL after the loop?  NULL
concatenated with anything is still NULL.  If any of the various fields
you're concatenating in could be NULL you'll need to guard against that.

----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "K Anderson" <msmouse@kittymail.com>
Subject: Re: [NOVICE] LOOping clobbering variable
Date: Sat, 1 Jan 2005 22:07:00 -0700

>
> On Sat, Jan 01, 2005 at 07:38:16PM -0800, K Anderson wrote:
>
> > The enclosed function returns a proper result only when the LOOP
> > and its enclosed functionality is removed from the function. Notice
> > near the return I have  xml=xml||'</transaction>';
> >
> > That should at the least return what ever is in xml with
> > </transaction> concatinated, or so I would hope. But it's empty.
>
> I'd guess that one of the values you're concatenating is NULL.
> Concatenating NULL with anything results in NULL, so if you assign
> that back to xml then all subsequent concatenations will also result
> in NULL.
>
> You can use COALESCE to convert NULLs to empty strings.
Thanks Michael that did the trick. I still don't get it though. Seems like bad behavior to say the NULL||</transaction>
resultsin NULL and not NULL</transaction> or even </transaction>. 

--
_______________________________________________
Get your free email from http://www.kittymail.com

Powered by Outblaze

On Sun, Jan 02, 2005 at 03:06:08PM -0800, K Anderson wrote:
>
> Seems like bad behavior to say the NULL||</transaction> results
> in NULL and not NULL</transaction> or even </transaction>.

Others will disagree; take it up with the standards committee.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/