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

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/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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.

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