I'm stuck - I just can't get this small FUNCT to run! - Mailing list pgsql-sql

<big><tt><font size="-1"><big>I'm also stuck on 7.4 for at least a few more months, but that's not part of the
problem.<br/> I've spent hours on this, cutting things out, etc., now I have to give it and me a break.<br /> Anything
standout to anyone?<br /><br /> ===================================<br /> FIRST the pgAdmin error message:<br />
===================================<br/><br /> ERROR:  syntax error at or near "loop"<br /> CONTEXT:  compile of
PL/pgSQLfunction "fill_advert_n_coupon" near line 92<br /><br /> ********** Error **********<br /><br /> ERROR: syntax
errorat or near "loop"<br /> SQL state: 42601<br /> Context: compile of PL/pgSQL function "fill_advert_n_coupon" near
line92<br /><br /> ==================================<br /> SECOND the code:<br />
==================================<br/> /*<br /><br /> Sales' info contains the fields { offer_title, _text, start,
stop,fineprint }.<br /> This function will take data from temp_salesimport and insert appropriately into<br /> tables
advert,advertdetail, and coupon.<br /><br /> -> The commented out command below was needed only once to assert a
referencecommerce transaction.<br /> -> The content of the insert must be matched by a query in the function
below:<br/> -> INSERT INTO commercetransaction (descrip) VALUES('Entry of Sales-gathered info while free');<br /><br
/>*/<br /> -------------------<br /> CREATE OR REPLACE FUNCTION fill_advert_n_coupon(varchar) RETURNS VOID AS '<br />  
<br/>   DECLARE daRec            RECORD ;<br />           vCommTransID          INT ;<br />          
vAdvertTypeID        INT ;<br />           vAdvertDetailTypeID   INT ;<br />           vFieldName           
VARCHAR(50);<br />           vBusID                BIGINT ;<br />           vBusOwnerID           BIGINT ;<br />
         vAdvertID             INT ;<br />           vValueText            VARCHAR(256) ;<br />          
vMaxSeq              INT ;<br />           vNextSeq              INT ;<br />           vValidFrom            DATE ;<br
/>          vValidTo              DATE ;<br />           vRestriction          VARCHAR(200) ;<br />   <br />   BEGIN<br
/>    <br />     select into vCommTransID id from commercetransaction where descrip=''Entry of Sales-gathered info
whilefree'';  -- =4510<br />   <br />     select into vAdvertTypeID id from adverttype where shortname=''CouponOffer''
;-- =1<br /><br />     select into vAdvertDetailTypeID id from advertdetailtype where shortname=''$1'' ; -- =2<br />  
<br/>     -- Options are title, text, start, stop, fineprint <br />     vFieldName= ''offer_'' || ''$1''<br />   <br />
   -- =====================================================================<br />     <br />     FOR daRec IN SELECT *
FROMtemp_salesimport WHERE offer_title<>'''' LOOP<br /><br />       vBusID=daRec.bus_id<br />       <br />      
selectinto vBusOwnerID businessownerid from business where id=vBusID ;<br />       <br />       <br />       IF
vFieldName=''offer_title''THEN<br />         select into vValueText offer_title     from temp_salesimport where
bus_id=vBusID;<br />       ELSIF vFieldName=''offer_text'' THEN<br />         select into vValueText offer_text     
fromtemp_salesimport where bus_id=vBusID ;<br />       ELSIF vFieldName=''offer_start'' THEN<br />         select into
vValueTextoffer_start     from temp_salesimport where bus_id=vBusID ;<br />       ELSIF vFieldName=''offer_stop''
THEN<br/>         select into vValueText offer_stop      from temp_salesimport where bus_id=vBusID ;<br />       ELSIF
vFieldName=''offer_fineprint''THEN<br />         select into vValueText offer_fineprint from temp_salesimport where
bus_id=vBusID;<br />       END IF ;<br />       <br />       <br />       <br />    -- Begin inserting into the
destinationtables advertdetail, advertdetailline, and coupon<br />    --
======================================================================================<br/>       if $1=''title'' or
$1=''text''then   -- Advert stuff<br />         <br /><br />         insert into advert (adverttypeid, businessid,
businessownerid,<br/>                        isactive, isenabled, active_date, expire_date, commercetransactionid)<br
/>            VALUES (vAdvertTypeID, vBusID, vBusOwnerID,<br />                    TRUE, TRUE,
''2010-11-03'',''2011-02-03'',vCommTransID);<br />         <br /><br />         select into vAdvertID MAX(id) from
advert<br/>               where adverttypeid=vAdvertTypeID <br />                 and businessid=vBusID<br />
               and businessownerid=vBusOwnerID <br />                 and isactive=TRUE and isenabled=TRUE ;<br />
       <br /><br />         select into vMaxSeq seq from advertdetail<br />          where advertid=vAdvertID and
advertdetailtype=vAdvertDetailTypeID;<br />         <br />         vNextSeq:=vMaxSeq+1 ;<br />   <br /><br />        
insertinto advertdetail values(vAdvertID, vAdvertDetailTypeID, vValueText, vNextSeq) ;<br />         <br />       else 
--Coupon stuff<br />         <br /><br />         select into vValidFrom, vValidTo, vRestriction<br />
                 offer_start, offer_stop, offer_fineprint<br />          from temp_salesimport<br />          where
bus_id=vBusID;<br /><br /><br />         insert into coupon (businessid, validfrom, validto, restriction)<br />
                    values(vBusID, vValidFrom, vValidTo, vRestriction) ;<br />         <br />       end if ;  -- title
ortext -> advert + advertdetail, else coupon<br />       <br />     end loop ;<br />     <br />     RETURN ;<br
/><br/>  -- END ; -- The Fantom one for BEGIN above.   <br /><br /> END ; ' LANGUAGE plpgsql<br />
-------------------<br/> select fill_advert_n_coupon('title') ;<br /></big></font></tt></big> <pre
class="moz-signature"cols="72"><big><tt>-- 
 

Ralph
_________________________
</tt></big></pre>