From: Ralph Smith <rsmith@10kinfo.com>
To: pgsql-sql@postgresql.org
Subject: I'm stuck - I just can't get this small FUNCT to run!
Date: Wed, 03 Nov 2010 17:00:18 -0700
Message-ID: <4CD1F792.6040907@10kinfo.com> (raw)
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
_________________________
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rsmith@10kinfo.com
Subject: Re: I'm stuck - I just can't get this small FUNCT to run!
In-Reply-To: <4CD1F792.6040907@10kinfo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox