Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id D09C21337B74 for ; Wed, 3 Nov 2010 21:00:45 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 37803-09 for ; Thu, 4 Nov 2010 00:00:34 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw05.intertain.com (gw05.intertain.com [209.101.231.5]) by mail.postgresql.org (Postfix) with ESMTP id 11F9D1337B6E for ; Wed, 3 Nov 2010 21:00:21 -0300 (ADT) Received: from [192.168.1.184] (softdnserr [::ffff:192.168.1.184]) (AUTH: LOGIN rsmith, TLS: TLSv1/SSLv3,256bits,AES256-SHA) by gw05.intertain.com with esmtp; Wed, 03 Nov 2010 17:00:18 -0700 id 00714003.4CD1F792.000007AA Message-ID: <4CD1F792.6040907@10kinfo.com> Date: Wed, 03 Nov 2010 17:00:18 -0700 From: Ralph Smith User-Agent: Thunderbird 2.0.0.24 (X11/20101027) MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: I'm stuck - I just can't get this small FUNCT to run! Content-Type: multipart/alternative; boundary="------------010406090905070705060504" Received-SPF: pass (Address passes the Sender Policy Framework) SPF=FROM; sender=rsmith@10kinfo.com; remoteip=::ffff:192.168.1.184; remotehost=softdnserr; helo=[192.168.1.184]; receiver=gw05.intertain.com; X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201011/5 X-Sequence-Number: 34743 This is a multi-part message in MIME format. --------------010406090905070705060504 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit I'm also stuck on 7.4 for at least a few more months, but that's not part of the problem. I've spent hours on this, cutting things out, etc., now I have to give it and me a break. Anything stand out to anyone? =================================== FIRST the pgAdmin error message: =================================== ERROR: syntax error at or near "loop" CONTEXT: compile of PL/pgSQL function "fill_advert_n_coupon" near line 92 ********** Error ********** ERROR: syntax error at or near "loop" SQL state: 42601 Context: compile of PL/pgSQL function "fill_advert_n_coupon" near line 92 ================================== SECOND the code: ================================== /* Sales' info contains the fields { offer_title, _text, start, stop, fineprint }. This function will take data from temp_salesimport and insert appropriately into tables advert, advertdetail, and coupon. -> The commented out command below was needed only once to assert a reference commerce transaction. -> The content of the insert must be matched by a query in the function below: -> INSERT INTO commercetransaction (descrip) VALUES('Entry of Sales-gathered info while free'); */ ------------------- CREATE OR REPLACE FUNCTION fill_advert_n_coupon(varchar) RETURNS VOID AS ' DECLARE daRec RECORD ; vCommTransID INT ; vAdvertTypeID INT ; vAdvertDetailTypeID INT ; vFieldName VARCHAR(50) ; vBusID BIGINT ; vBusOwnerID BIGINT ; vAdvertID INT ; vValueText VARCHAR(256) ; vMaxSeq INT ; vNextSeq INT ; vValidFrom DATE ; vValidTo DATE ; vRestriction VARCHAR(200) ; BEGIN select into vCommTransID id from commercetransaction where descrip=''Entry of Sales-gathered info while free''; -- =4510 select into vAdvertTypeID id from adverttype where shortname=''CouponOffer'' ; -- =1 select into vAdvertDetailTypeID id from advertdetailtype where shortname=''$1'' ; -- =2 -- Options are title, text, start, stop, fineprint vFieldName= ''offer_'' || ''$1'' -- ===================================================================== FOR daRec IN SELECT * FROM temp_salesimport WHERE offer_title<>'''' LOOP vBusID=daRec.bus_id select into vBusOwnerID businessownerid from business where id=vBusID ; IF vFieldName=''offer_title'' THEN select into vValueText offer_title from temp_salesimport where bus_id=vBusID ; ELSIF vFieldName=''offer_text'' THEN select into vValueText offer_text from temp_salesimport where bus_id=vBusID ; ELSIF vFieldName=''offer_start'' THEN select into vValueText offer_start from temp_salesimport where bus_id=vBusID ; ELSIF vFieldName=''offer_stop'' THEN select into vValueText offer_stop from temp_salesimport where bus_id=vBusID ; ELSIF vFieldName=''offer_fineprint'' THEN select into vValueText offer_fineprint from temp_salesimport where bus_id=vBusID ; END IF ; -- Begin inserting into the destination tables advertdetail, advertdetailline, and coupon -- ====================================================================================== if $1=''title'' or $1=''text'' then -- Advert stuff insert into advert (adverttypeid, businessid, businessownerid, isactive, isenabled, active_date, expire_date, commercetransactionid) VALUES (vAdvertTypeID, vBusID, vBusOwnerID, TRUE, TRUE, ''2010-11-03'',''2011-02-03'',vCommTransID) ; select into vAdvertID MAX(id) from advert where adverttypeid=vAdvertTypeID and businessid=vBusID and businessownerid=vBusOwnerID and isactive=TRUE and isenabled=TRUE ; select into vMaxSeq seq from advertdetail where advertid=vAdvertID and advertdetailtype=vAdvertDetailTypeID ; vNextSeq:=vMaxSeq+1 ; insert into advertdetail values(vAdvertID, vAdvertDetailTypeID, vValueText, vNextSeq) ; else -- Coupon stuff select into vValidFrom, vValidTo, vRestriction offer_start, offer_stop, offer_fineprint from temp_salesimport where bus_id=vBusID ; insert into coupon (businessid, validfrom, validto, restriction) values(vBusID, vValidFrom, vValidTo, vRestriction) ; end if ; -- title or text -> advert + advertdetail, else coupon end loop ; RETURN ; -- END ; -- The Fantom one for BEGIN above. END ; ' LANGUAGE plpgsql ------------------- select fill_advert_n_coupon('title') ; -- Ralph _________________________ --------------010406090905070705060504 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit I'm also stuck on 7.4 for at least a few more months, but that's not part of the problem.
I've spent hours on this, cutting things out, etc., now I have to give it and me a break.
Anything stand out to anyone?

===================================
FIRST the pgAdmin error message:
===================================

ERROR:  syntax error at or near "loop"
CONTEXT:  compile of PL/pgSQL function "fill_advert_n_coupon" near line 92

********** Error **********

ERROR: syntax error at or near "loop"
SQL state: 42601
Context: compile of PL/pgSQL function "fill_advert_n_coupon" near line 92

==================================
SECOND the code:
==================================
/*

Sales' info contains the fields { offer_title, _text, start, stop, fineprint }.
This function will take data from temp_salesimport and insert appropriately into
tables advert, advertdetail, and coupon.

-> The commented out command below was needed only once to assert a reference commerce transaction.
-> The content of the insert must be matched by a query in the function below:
-> INSERT INTO commercetransaction (descrip) VALUES('Entry of Sales-gathered info while free');

*/
-------------------
CREATE OR REPLACE FUNCTION fill_advert_n_coupon(varchar) RETURNS VOID AS '
 
  DECLARE daRec            RECORD ;
          vCommTransID          INT ;
          vAdvertTypeID         INT ;
          vAdvertDetailTypeID   INT ;
          vFieldName            VARCHAR(50) ;
          vBusID                BIGINT ;
          vBusOwnerID           BIGINT ;
          vAdvertID             INT ;
          vValueText            VARCHAR(256) ;
          vMaxSeq               INT ;
          vNextSeq              INT ;
          vValidFrom            DATE ;
          vValidTo              DATE ;
          vRestriction          VARCHAR(200) ;
 
  BEGIN
   
    select into vCommTransID id from commercetransaction where descrip=''Entry of Sales-gathered info while free'';  -- =4510
 
    select into vAdvertTypeID id from adverttype where shortname=''CouponOffer'' ; -- =1

    select into vAdvertDetailTypeID id from advertdetailtype where shortname=''$1'' ; -- =2
 
    -- Options are title, text, start, stop, fineprint
    vFieldName= ''offer_'' || ''$1''
 
    -- =====================================================================
   
    FOR daRec IN SELECT * FROM temp_salesimport WHERE offer_title<>'''' LOOP

      vBusID=daRec.bus_id
     
      select into vBusOwnerID businessownerid from business where id=vBusID ;
     
     
      IF vFieldName=''offer_title'' THEN
        select into vValueText offer_title     from temp_salesimport where bus_id=vBusID ;
      ELSIF vFieldName=''offer_text'' THEN
        select into vValueText offer_text      from temp_salesimport where bus_id=vBusID ;
      ELSIF vFieldName=''offer_start'' THEN
        select into vValueText offer_start     from temp_salesimport where bus_id=vBusID ;
      ELSIF vFieldName=''offer_stop'' THEN
        select into vValueText offer_stop      from temp_salesimport where bus_id=vBusID ;
      ELSIF vFieldName=''offer_fineprint'' THEN
        select into vValueText offer_fineprint from temp_salesimport where bus_id=vBusID ;
      END IF ;
     
     
     
   -- Begin inserting into the destination tables advertdetail, advertdetailline, and coupon
   -- ======================================================================================
      if $1=''title'' or $1=''text'' then   -- Advert stuff
       

        insert into advert (adverttypeid, businessid, businessownerid,
                       isactive, isenabled, active_date, expire_date, commercetransactionid)
            VALUES (vAdvertTypeID, vBusID, vBusOwnerID,
                   TRUE, TRUE, ''2010-11-03'',''2011-02-03'',vCommTransID) ;
       

        select into vAdvertID MAX(id) from advert
              where adverttypeid=vAdvertTypeID
                and businessid=vBusID
                and businessownerid=vBusOwnerID
                and isactive=TRUE and isenabled=TRUE ;
       

        select into vMaxSeq seq from advertdetail
         where advertid=vAdvertID and advertdetailtype=vAdvertDetailTypeID ;
       
        vNextSeq:=vMaxSeq+1 ;
 

        insert into advertdetail values(vAdvertID, vAdvertDetailTypeID, vValueText, vNextSeq) ;
       
      else  -- Coupon stuff
       

        select into vValidFrom, vValidTo, vRestriction
                  offer_start, offer_stop, offer_fineprint
         from temp_salesimport
         where bus_id=vBusID ;


        insert into coupon (businessid, validfrom, validto, restriction)
                     values(vBusID, vValidFrom, vValidTo, vRestriction) ;
       
      end if ;  -- title or text -> advert + advertdetail, else coupon
     
    end loop ;
   
    RETURN ;

 -- END ; -- The Fantom one for BEGIN above.  

END ; ' LANGUAGE plpgsql
-------------------
select fill_advert_n_coupon('title') ;
 
-- 

Ralph
_________________________
--------------010406090905070705060504--