Re: need some help with pl-pgsql - Mailing list pgsql-general

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;