Date: Wed, 11 Dec 2002 13:56:40 +0100 (CET)
From: Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
To: Geoff <geoff@metalogicplc.com>
Cc: "Pgsql-Admin (E-mail)" <pgsql-admin@postgresql.org>
Subject: Re: Function Problem 
In-Reply-To: <FDCF842C8B1DD311833C0090277C04B518C27B@augusta.planmatics.co.uk>
Message-ID: <Pine.LNX.4.44.0212111346110.25782-100000@server>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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
>