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