-- Function: testfunc2(m_minqty int8)

-- DROP FUNCTION testfunc2(m_minqty int8);

CREATE OR REPLACE FUNCTION testfunc2(m_minqty int8)
RETURNS SETOF record AS
$BODY$



SELECT Item.ItemCode, Item.Qty,
(item.itemcode || item.itemcode)::varchar as yy,
0::int4 as prod ,
COALESCE(SUM(TranItem.Qty),0)::int8 AS SumQty
FROM Item
LEFT JOIN TranItem
ON TranItem.ItemCode = Item.ItemCode
GROUP BY Item.itemcode, Item.Qty

$BODY$
LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION testfunc2(m_minqty int8) OWNER TO "postgres-su";




-- Function: testfunc()

-- DROP FUNCTION testfunc();

CREATE OR REPLACE FUNCTION testfunc()
RETURNS SETOF record AS
$BODY$

declare
m_ItemCode varchar(10);
m_Qty INT8;

rec record;

m_runningTot int8;
begin


m_runningtot = 0;
FOR rec
IN SELECT ItemCode, Qty, (itemcode || itemcode)::varchar as yy, 0::int4 as prod FROM Item order by itemcode desc
LOOP
m_runningtot = m_runningtot + rec.qty;
rec.prod = m_runningtot;
RETURN NEXT rec;
END LOOP;



RETURN;

end;

$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION testfunc() OWNER TO "postgres-su";



/*
select * from testfunc2(7) as x(
itemcode varchar(8),
qty int8,
yy varchar,
prod int4,
SumQty int8);
*/

SELECT * FROM TESTFUNC2(1) AS X(itemcode varchar(8), qty int8, yy varchar, Prod int4);