Thread: Function Problem
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
Can anyone see what I've done wrong in there?
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. | |
| |-----------------------------------------------------| |
\ | | /
\ / \ /
`---' `---'
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
>
On Wed, 2002-12-11 at 07:38, Geoff wrote: > > RETURN NULL; > > END > > ' LANGUAGE 'plpgsql' ; > Look over Jakub's email, it's on the right track. FWIW, you also need a ; after your END statement, which I believe is the actual error your seeing. Robert Treat
Thanks guys, I've cracked it now... yeah, a lot of syntax errors, but to be honest, I never got to look at the pl syntax page. Thanks to Jakub for the link, it helped solve the problem... thanks again.. Geoff -----Original Message----- From: Robert Treat [mailto:xzilla@users.sourceforge.net] Sent: 11 December 2002 15:57 To: geoff@metalogicplc.com Cc: Pgsql-Admin (E-mail) Subject: Re: [ADMIN] Function Problem On Wed, 2002-12-11 at 07:38, Geoff wrote: > > RETURN NULL; > > END > > ' LANGUAGE 'plpgsql' ; > Look over Jakub's email, it's on the right track. FWIW, you also need a ; after your END statement, which I believe is the actual error your seeing. Robert Treat