Skip site navigation (1) Skip section navigation (2)

Re: need some help with pl-pgsql

From: Adrian Klaver <aklaver(at)comcast(dot)net>
To: justin <justin(at)emproshunts(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: need some help with pl-pgsql
Date: 2008-12-23 23:36:46
Message-ID: 1849665526.601681230075406181.JavaMail.root@sz0030a.emeryville.ca.mail.comcast.net (view raw or flat)
Thread:
Lists: pgsql-general
----- "justin" <justin(at)emproshunts(dot)com> wrote:

> Adrian Klaver wrote:
> 
> 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;

Hmmm. A couple of suggestions while I ponder this more. For clarity sake decide on a case scheme. I start getting twitchy when I see pwoid and pWoid refer to the same thing. This depends on case folding being consistent, not something I like to count on. Second you might want to put in a few RAISE NOTICE statements to see what values are being pulled from the db for wo_wipvalue and wo_qtyord and what is being supplied to the WHERE clause for pWoid.

Adrian Klaver
aklaver(at)comcast(dot)net

In response to

Responses

pgsql-general by date

Next:From: Adrian KlaverDate: 2008-12-24 03:45:57
Subject: Re: need some help with pl-pgsql
Previous:From: justinDate: 2008-12-23 22:33:22
Subject: Re: need some help with pl-pgsql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group