Re: Function Problem - Mailing list pgsql-admin
From | Jakub Ouhrabka |
---|---|
Subject | Re: Function Problem |
Date | |
Msg-id | Pine.LNX.4.44.0212111346110.25782-100000@server Whole thread Raw |
In response to | Function Problem ("Geoff" <geoff@metalogicplc.com>) |
List | pgsql-admin |
hi, > I've got this function which works off a trigger. > The trigger is calling the function ok, but I get this error. > <error> > NOTICE: plpgsql: ERROR during compile of mon_sum_update near line 43 > ERROR: parse error at or near "" > > Now line 43 is this either the return null or end statement at the end of > the function... > > RETURN NULL; > > END unfortunately, the error messages aren't always accurate in plpgsql... > Can anyone see what I've done wrong in there? i think there are few mistakes: there is missing "END IF;" in all IF statements, it should look like this: IF (condition) THEN statement; END IF; also conditions aren't always perfect, you should use "=" instead of "==" for comparison in plpgsql. don't use aposthrophes inside the function, it isn't necessary for expressions like new.field... if you really want them, use double aposthrophes "''" - you must quote them inside the plpgsql function. and may be there are some other mistakes. i would suggest to study syntax of plpgsql at http://www.postgresql.org/idocs/index.php?plpgsql.html carefully. it can save you a lot of time when debugging functions - the compiler isn't very wise when reporting errors... hth, kuba > > TIA > > Geoff > > > > Here is my trigger and function. > > trigger > ====== > CREATE TRIGGER doc_status_trig AFTER UPDATE ON document_status FOR EACH ROW > EXECUTE PROCEDURE mon_sum_update() > > function > ====== > CREATE FUNCTION mon_sum_upd () RETURN OPAQUE AS ' > > BEGIN > > -- Ensure we have a record that is valid . > > IF ( ! NEW.direction && NEW.direction && NEW.msgtype && NEW.status ) > THEN > RETURN NULL; > > > -- Ensure the record exists in the monitor_summary table. > > IF ( ! EXISTS SELECT * FROM monitor_summary WHERE unit = NEW.unit and > msgtype = NEW.msgtype and direction = NEW.direction and status = > NEW.status ) > THEN > INSERT INTO monitor_summary ( version, cdate, mdate, direction, unit, > msgtype, status ) VALUES ( 1, 'now', 'now', 'NEW.direction', 'NEW.unit', > 'NEW.msgtype', 'NEW.status' ); > > -- Ensure OLD and NEW status's are different. > > IF ( NEW.status == OLD.status ) > THEN > RETURN NULL; > > > -- Update the OLD status record. ( -1 ) > > UPDATE monitor_summary > SET > total = total - 1 > WHERE > direction = OLD.direction AND unit = OLD.unit AND msgtype = OLD.msgtype AND > status = OLD.status ; > > -- Update the NEW status record. ( +1 ) > > UPDATE monitor_summary > SET > total = total + 1 > WHERE > direction = OLD.direction AND unit = OLD.unit AND msgtype = OLD.msgtype AND > status = NEW.status ; > > RETURN NULL; > > END > > ' LANGUAGE 'plpgsql' ; > > > I'm using pgAdminII to insert this function, and it goes in ok.. > > - Geoff Ellis > - +44(0)2476678484 > > .-----------------------------------------------------------------. > / .-. This message is intended only for the person or .-. \ > | / \ entity to which it is addressed and may contain / \ | > | |\_. | confidential and/or privileged material. Any | ._/| | > |\| | /| review, retransmission, dissemination or other |\ | |/| > | `---' | use of, or taking of any action in reliance upon, | `---' | > | | this information by persons or entities other than | | > | | the intended recipient is prohibited. If you get | | > | | this message in error please contact the sender | | > | | by return e-mail and delete the message from your | | > | | computer. Any opinions contained in this message | | > | | are those of the author and are not given or | | > | | endorsed by Metalogic PLC unless otherwise clearly | | > | | indicated in this message and the authority of the | | > | | author to bind Metalogic is duly verified. | | > | | | | > | | Metalogic PLC accepts no liability for any errors | | > | | or omissions in the context of this message which | | > | | arise as a result of internet transmission. | | > | |-----------------------------------------------------| | > \ | | / > \ / \ / > `---' `---' > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
pgsql-admin by date: