Re: need some help with pl-pgsql

Adrian Klaver wrote:
> On Tuesday 23 December 2008 1:04:49 pm justin wrote:
>
>> have a function written in pgsql it runs just fine, except its doing
>> something really stupid.
>>
>> The function runs just fine till this select statement
>>
>> Select (wo_wipvalue/wo_qtyord)
>> into _TotalCost
>> from wo
>> where wo_id = pWoid ;
>>
>> This goes to the work order header table to gets the current value
>> divided by current qty thats been completed so far, then sticks the
>> value into _TotalCost variable based on the parameter passed into
>> variable pWoid.
>>
>> problem is it returns NULL which is impossible as i can manually run
>> the select statement and get the $1.589445
>>
>> I have no idea how to fix this problem going forward???
>>
>>
>> and the database is 8.3.5 running Ubuntu 8.0 lts
>>
>
> Would help to see the whole function. Also make sure you did not name one of
> the variables the same as a column name, this will confuse plpgsql. Are you
> using the same value for wo_id in the function as in the manual select
> statement?
>

First the funciton has been running for months and never has had a
problem. No changes to the database scheme. Second use variable naming
scheme completely different from column names. _ always is the first
character in variables. p is always the first character in passed
parameters.

Take a look at the screen shot and be in aw as i am

postproduction(pwoid integer, pqty numeric, pbackflush boolean,
pbackflushoperations boolean, pitemlocseries integer, psuuser text,
prnuser text, pdate date)
RETURNS integer AS
$BODY$ DECLARE
_woNumber TEXT;
_itemlocSeries INTEGER;

_parentQty NUMERIC;
_qty NUMERIC;
_TotalCost numeric;

BEGIN

IF (pQty <= 0) THEN
RETURN 0;
END IF;

IF ( ( SELECT wo_status
FROM wo
WHERE (wo_id=pWoid) ) NOT IN ('R','E','I') ) THEN
RETURN -1;
END IF;

--If this is item type Job then we are using the wrong function
SELECT item_type INTO _check
FROM wo, itemsite, item
WHERE ((wo_id=pWoid)
AND (wo_itemsite_id=itemsite_id)
AND (itemsite_item_id=item_id)
AND (item_type = 'J'));

IF (FOUND) THEN
RAISE EXCEPTION 'Work orders for job items are posted when
quantities are shipped on the associated sales order';
END IF;

SELECT formatWoNumber(pWoid) INTO _woNumber;

SELECT roundQty(item_fractional, pQty) INTO _parentQty
FROM wo,
itemsite,
item
WHERE ((wo_itemsite_id=itemsite_id)
AND (itemsite_item_id=item_id)
AND (wo_id=pWoid));

-- Create the material receipt transaction
IF (pItemlocSeries = 0) THEN
SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
ELSE
_itemlocSeries = pItemlocSeries;
END IF;

--Lets get Wips Current total cost
Select (wo_wipvalue/wo_qtyord)
into _TotalCost
from wo
where wo_id = pWoid ;
--Moves WIP into Inventory.
SELECT postInvTrans( itemsite_id,
'RM',
_parentQty,
'W/O',
'WO',
_woNumber,
'',
'Receive Inventory from Manufacturing',
costcat_asset_accnt_id,
costcat_wip_accnt_id,
_itemlocSeries,
true,
_TotalCost,
pDate::timestamp ) INTO _invhistid
FROM wo,
itemsite,
costcat
WHERE ( (wo_itemsite_id=itemsite_id)
AND (itemsite_costcat_id=costcat_id)
AND (wo_id=pWoid) );

-- Increase this W/O's received qty decrease its WIP value
UPDATE wo SET
wo_qtyrcv = (wo_qtyrcv + _parentQty),
wo_wipvalue = (wo_wipvalue - (_TotalCost*pQty))
FROM itemsite,
item
WHERE ((wo_itemsite_id=itemsite_id)
AND (itemsite_item_id=item_id)
AND (wo_id=pWoid));

-- Make sure the W/O is at issue status
UPDATE wo SET
wo_status='I'
WHERE (wo_id=pWoid);

RETURN _itemlocSeries;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION postproduction(integer, numeric, boolean, boolean,
integer, text, text, date) OWNER TO justin;
GRANT EXECUTE ON FUNCTION postproduction(integer, numeric, boolean,
boolean, integer, text, text, date) TO justin;
GRANT EXECUTE ON FUNCTION postproduction(integer, numeric, boolean,
boolean, integer, text, text, date) TO public;