Stored Procedures

To fill the tables with test data you can use the stored procedure generateorders

CREATE OR REPLACE FUNCTION generateorders(numberoforders integer, startnumber integer)
  RETURNS void AS
$BODY$
 DECLARE counter INT ;
	 numberOfLines INT ;	
	 orderPK VARCHAR(20);
	
BEGIN
	numberOfLines = 2;
	for counter in startNumber .. (startNumber + numberOfOrders) LOOP
	   orderPK = 'SAL_' || counter;
	   case 
		when numberOfLines = 0 then
			INSERT INTO Orders (orderNumber, orderStatus, orderDateTime, customer, lastModification)
			VALUES (orderPK, 'Open', now(), 'Johnson', now());
		when numberOfLines = 1 then
			INSERT INTO Orders (orderNumber, orderStatus, orderDateTime, customer, lastModification)
			VALUES (orderPK, 'Open', now(), 'Jones', now());
			INSERT INTO OrderLines (orderNumber, lineNumber, item, quantity, price)
			VALUES (orderPK, 1, 'Laptop', 1, 600.00);
		when numberOfLines = 2 then
		        INSERT INTO Orders (orderNumber, orderStatus, orderDateTime, customer, lastModification)
		        VALUES (orderPK, 'Open', now(), 'Smith', now());
		        INSERT INTO OrderLines (orderNumber, lineNumber, item, quantity, price)
		        VALUES (orderPK, 1, 'Desk', 1, 150.00);
		        INSERT INTO OrderLines (orderNumber, lineNumber, item, quantity, price)
		        VALUES (orderPK, 2, 'Chair', 4, 25.00);
		end case;
	end loop;
	perform setval('g_orders_id', counter);
END;


 $BODY$
  LANGUAGE plpgsql;

To read data in XML format from the Orders and orderliness table, use the  getallorders stored procedure:
CREATE OR REPLACE FUNCTION getallorders(t_age interval)
  RETURNS text AS
$BODY$
	declare ids text[];
begin
	select into ids array(select ordernumber from orders where now() - lastmodification < t_age);
	return getorders(ids);
end;

To use the XML data from an incoming BOD as input and to write to the database, use the stored procedure processbod. This procedure creates a new Order if it does not exist. If the Order exists, the procedure will update the order.

CREATE OR REPLACE FUNCTION processbod(xmlcontent xml)
  RETURNS text AS
$BODY$

declare  

	 orders xml[];
	 order_xml xml;
	 tempVal text;
	 -- variables for the order header
	 
	 id text;
	 orderStatus_		varchar (20);
	 orderDatetimeStr text;
	 orderDatetime_		timestamp;
	 customer_		varchar (20);
	 processedIds 		text[];	
	
	--	orderLine	
	lineIds 		text[];
	lineId 			text;
	item_			varchar(20);
	quantity_		integer;
	price_			numeric(18,2);

begin
	orders := xpath('//Order', xmlcontent);
	
	foreach order_xml in array orders
	loop
		--return xpath('//Order/OrderHeader/OrderStatus/text()', order_xml);
		id := array_to_string(xpath('//DocumentId/text()', order_xml),'');
		orderStatus_ := array_to_string(xpath('//Order/OrderHeader/OrderStatus/text()', order_xml), ',');
		orderDatetimeStr := array_to_string(xpath('//Order/OrderHeader/OrderDate/text()', order_xml), ',');
		customer_ := array_to_string(xpath('//Order/OrderHeader/Customer/text()', order_xml), ',');
		
		if orderDatetimeStr is null or orderDatetimeStr = '' then
			orderDatetime_ =  now();
		else
			orderDatetime_ = cast(orderDatetimeStr as timestamp with time zone);
		end if;
		
		if id = '' then
			id := 'SAL_' ||nextval('g_orders_id');
			orderDatetime_ = now();
		end if;
			
		select into tempVal ordernumber from Orders where ordernumber = id;
		if not found then
			insert into orders(ordernumber, orderstatus, orderdatetime, customer, lastmodification) values (id, orderStatus_, orderDatetime_, customer_, now());
		else 
			update orders set orderstatus = orderStatus_, orderdatetime = orderDatetime_, customer = customer_ , lastmodification = now()
			where ordernumber = id;
		end if;
		processedIds := array_append ( processedIds, id);
		
		lineIds := xpath('//Order/OrderLines/OrderLine/OrderLineNumber/text()', order_xml);

		if  array_ndims(lineIds) >= 1 then
			foreach lineId in array lineIds
			loop
				item_ := array_to_string(xpath('//Order/OrderLines/OrderLine[OrderLineNumber=' || lineId || ']/Item/text()', order_xml),',');
				quantity_ := array_to_string(xpath('//Order/OrderLines/OrderLine[OrderLineNumber=' || lineId || ']/Quantity/text()', order_xml),',');
				price_ := array_to_string(xpath('//Order/OrderLines/OrderLine[OrderLineNumber=' || lineId || ']/Price/text()', order_xml),',');
				
				select into tempVal linenumber from orderlines where ordernumber = id and linenumber = cast (lineId as integer);
				if not found then
					insert into orderlines (ordernumber, linenumber, item, quantity, price) values (id, cast (lineId as integer), item_, quantity_, price_);
					
				else 
					update orderlines set item = item_, quantity = quantity_ , price = price_
					where ordernumber = id and linenumber = cast (lineId as integer);
				end if;
			end loop;
		end if;

	end loop;

return getOrders(processedIds);
end;
$BODY$
  LANGUAGE plpgsql;