From: | "K Anderson" <msmouse(at)kittymail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | LOOping clobbering variable |
Date: | 2005-01-02 03:38:16 |
Message-ID: | 20050102033816.F40421027BE@ws3.hk5.outblaze.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2005-01-02 05:07:00 | Re: LOOping clobbering variable |
Previous Message | Jason Dixon | 2005-01-01 19:33:48 | Re: Grant on multiple objects |