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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match