pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedI'm stuck - I just can't get this small FUNCT to run!
4+ messages / 3 participants
[nested] [flat]
* I'm stuck - I just can't get this small FUNCT to run!
@ 2010-11-04 00:00 Ralph Smith <rsmith@10kinfo.com>
0 siblings, 2 replies; 4+ messages in thread
From: Ralph Smith @ 2010-11-04 00:00 UTC (permalink / raw)
To: pgsql-sql
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
_________________________
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: I'm stuck - I just can't get this small FUNCT to run!
@ 2010-11-04 00:09 bricklen <bricklen@gmail.com>
parent: Ralph Smith <rsmith@10kinfo.com>
1 sibling, 1 reply; 4+ messages in thread
From: bricklen @ 2010-11-04 00:09 UTC (permalink / raw)
To: Ralph Smith <rsmith@10kinfo.com>; +Cc: pgsql-sql
You appear to be missing a trailing semi-colons.
On Wed, Nov 3, 2010 at 5:00 PM, Ralph Smith <rsmith@10kinfo.com> wrote:
Here:
> vFieldName= ''offer_'' || ''$1''
and here:
> vBusID=daRec.bus_id
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: I'm stuck - I just can't get this small FUNCT to run!
@ 2010-11-04 03:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ralph Smith <rsmith@10kinfo.com>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2010-11-04 03:50 UTC (permalink / raw)
To: Ralph Smith <rsmith@10kinfo.com>; +Cc: pgsql-sql
Ralph Smith <rsmith@10kinfo.com> writes:
> I'm also stuck on 7.4 for at least a few more months, but that's not
> part of the problem.
... well, actually, it is; because newer versions give considerably more
helpful syntax error messages. I tried plugging this function into
a current version, and got
syntax error at or near "daRec"
LINE 34: FOR daRec IN SELECT * FROM temp_salesimport WHERE offer_...
^
which was at least close enough to the problem (the missing semi on the
previous line) to be of some use, unlike the error pointing at line 92.
Similarly, after fixing the first missing semi I got
ERROR: syntax error at or near "select"
LINE 38: select into vBusOwnerID businessownerid from business ...
^
which is just after the other one.
If you aren't in a position to move your production DB yet, you might
at least consider using a newer version for development. That has its
own hazards of course, like accidentally using features that don't exist
in 7.4, but it could save you a lot of time in cases like this.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: I'm stuck - I just can't get this small FUNCT to run!
@ 2010-11-04 20:07 Ralph Smith <rsmith@10kinfo.com>
parent: bricklen <bricklen@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Ralph Smith @ 2010-11-04 20:07 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Thank you both. I stared and stared and stared, and apparently I needed
to take a walk around the building
I have repeatedly asked about an upgrade, but as you mentioned Tom, it's
a compatibility issue and concern that remote programmers might end out
making more work for themselves. <<shrug>>
If I did a local install of PG 8.X (which is what I hear we'll be going
to), not connected to the real DB, would it be functional enough to give
me the better messages?
Again, thank you both,
Ralph
---------------------------------------------------------------
bricklen wrote:
> You appear to be missing a trailing semi-colons.
> Here:
> vFieldName= ''offer_'' || ''$1''
> and here: vBusID=daRec.bus_id
-------------------------------------------------------
Ralph Smith <rsmith@10kinfo.com> writes:
> > I'm also stuck on 7.4 for at least a few more months, but that's not
> > part of the problem.
>
... well, actually, it is; because newer versions give considerably more
helpful syntax error messages. I tried plugging this function into
a current version, and got
syntax error at or near "daRec"
LINE 34: FOR daRec IN SELECT * FROM temp_salesimport WHERE offer_...
^
which was at least close enough to the problem (the missing semi on the
previous line) to be of some use, unlike the error pointing at line 92.
Similarly, after fixing the first missing semi I got
ERROR: syntax error at or near "select"
LINE 38: select into vBusOwnerID businessownerid from business ...
^
which is just after the other one.
If you aren't in a position to move your production DB yet, you might
at least consider using a newer version for development. That has its
own hazards of course, like accidentally using features that don't exist
in 7.4, but it could save you a lot of time in cases like this.
regards, tom lane
--
Ralph
_________________________
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-11-04 20:07 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-04 00:00 I'm stuck - I just can't get this small FUNCT to run! Ralph Smith <rsmith@10kinfo.com>
2010-11-04 00:09 ` bricklen <bricklen@gmail.com>
2010-11-04 20:07 ` Ralph Smith <rsmith@10kinfo.com>
2010-11-04 03:50 ` Tom Lane <tgl@sss.pgh.pa.us>
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