error on last line of function

Started by Matthew Nuzumover 23 years ago3 messagesgeneral
Jump to latest
#1Matthew Nuzum
cobalt@bearfruit.org

Hello, I'm debugging a trigger that isn't working. It's supposed to run
the function delete_page() after a delete on table "pages". It's
producing the error:
# delete from pages where pageid=3550;
NOTICE: plpgsql: ERROR during compile of delete_page near line 81
ERROR: parse error at or near ";"

Line 81 is the last line of the function and merely says: END;

Maybe some fresh sets of eyes can give me some help with this? Here is
the function, any help is appreciated...

DECLARE
temp record;
BEGIN
IF old.page_type = ''f''
THEN
IF old.ftr_type = ''adf''
THEN
-- adf features store id in ftr_data
SELECT INTO temp delete_adf(old.ftr_data);
ELSE IF old.ftr_type = ''cal''
THEN
-- cal features do not store id in ftr_data
-- get the id
SELECT INTO temp * FROM ftr_cal_calendar
WHERE accountid = old.accountid AND name = substr(old.path,2);
SELECT INTO temp delete_cal(temp.calendarid);
ELSE IF old.ftr_type = ''cyu''
THEN
-- cyu stores id in ftr_data
SELECT INTO temp delete_cyu(old.ftr_data);
ELSE IF old.ftr_type = ''fa2'' OR old.ftr_type = ''faq''
THEN
-- these store id in ftr_data
SELECT INTO temp delete_faq(old.ftr_data);
ELSE IF old.ftr_type = ''fdl''
THEN
-- fdl features do not store id in ftr_data
-- get the id
SELECT INTO temp * FROM ftr_fdl
WHERE accountid = old.accountid AND name = substr(old.path,2);
SELECT INTO temp delete_fdl(temp.fdlid);
ELSE IF old.ftr_type = ''frm''
THEN
-- frm features do not store id in ftr_data
-- get the id
SELECT INTO temp * FROM ftr_frm
WHERE accountid = old.accountid AND name = substr(old.path,2);
SELECT INTO temp delete_frm(temp.id);
ELSE IF old.ftr_type = ''gal''
THEN
-- galeries do not store id in ftr_data
-- get the id
SELECT INTO temp * FROM ftr_gal
WHERE accountid = old.accountid AND name = substr(old.path, 2);
SELECT INTO temp delete_gal(temp.galleryid);
ELSE IF old.ftr_type = ''htm''
THEN
-- these store id in ftr_data
SELECT INTO temp delete_htm(old.ftr_data);
ELSE IF old.ftr_type = ''mbo''
THEN
-- these use a combination of ftr_data
-- and accountid. Deleting all matching records
DELETE FROM ftr_mbo_members
WHERE accountid = old.accountid AND realm = old.ftr_data;
ELSE IF old.ftr_type = ''rfl''
THEN
-- these store id in ftr_data
SELECT INTO temp delete_rfl(old.ftr_data);
ELSE IF old.ftr_type = ''shp''
THEN
-- these do not store the id in ftr_data
SELECT INTO temp "ID" as id FROM ftr_shp
WHERE accountid = old.accountid AND cartname = substr(old.path,
2);
SELECT INTO temp delete_shp(temp.id);
ELSE IF old.ftr_type = ''sta''
THEN
-- these do not store the id in ftr_data
SELECT INTO temp * FROM ftr_sta_directories
WHERE accountid = old.accountid and name = substr(old.path, 2);
SELECT INTO temp delete_sta(temp.staffid);
ELSE IF old.ftr_type = ''stm''
THEN
-- these do not store the id in ftr_data
SELECT INTO temp * FROM ftr_stm
WHERE accountid = old.accountid AND name = substr(old.path, 2);
SELECT INTO temp delete_stm(temp.stmid);
END IF;
END IF;
RETURN old;
END;

#2Richard Huxton
dev@archonet.com
In reply to: Matthew Nuzum (#1)
Re: error on last line of function

On Saturday 23 Nov 2002 3:38 am, Matthew Nuzum wrote:

NOTICE: plpgsql: ERROR during compile of delete_page near line 81
ERROR: parse error at or near ";"

Line 81 is the last line of the function and merely says: END;

Ah, plpgsql's error reporting. Not always the most useful guide, though I
don't think it's easily changed.

DECLARE
temp record;
BEGIN
IF old.page_type = ''f''
THEN
IF old.ftr_type = ''adf''
THEN
-- adf features store id in ftr_data
SELECT INTO temp delete_adf(old.ftr_data);
ELSE IF old.ftr_type = ''cal''
THEN
-- cal features do not store id in ftr_data
-- get the id
SELECT INTO temp * FROM ftr_cal_calendar
WHERE accountid = old.accountid AND name = substr(old.path,2);
SELECT INTO temp delete_cal(temp.calendarid);
ELSE IF old.ftr_type = ''cyu''
THEN
-- cyu stores id in ftr_data
SELECT INTO temp delete_cyu(old.ftr_data);
ELSE IF old.ftr_type = ''fa2'' OR old.ftr_type = ''faq''

I think the problem is you're missing the "END IF" from all the intermediate
IFs. I don't think there actually is an "ELSEIF" structure, it's just an ELSE
followed by an IF, so the IF needs to be concluded as normal. From the
manual:

IF demo_row.sex = ''m'' THEN
pretty_sex := ''man'';
ELSE
IF demo_row.sex = ''f'' THEN
pretty_sex := ''woman'';
END IF;
END IF;

See if that helps you any. Of course there may be a typo somewhere else too.
I'd suggest reducing the function to a single IF and expanding one block at a
time to spot the problem.

--
Richard Huxton

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Richard Huxton (#2)
Re: error on last line of function

Richard Huxton <dev@archonet.com> writes:

I think the problem is you're missing the "END IF" from all the intermediate
IFs. I don't think there actually is an "ELSEIF" structure, it's just an ELSE
followed by an IF, so the IF needs to be concluded as normal.

Recent releases (at least 7.2 and later) have an ELSIF abbrevation too.
See
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/plpgsql-control-structures.html

regards, tom lane