|
Howdy,
I'm new to triggers and functions,
and am having trouble doing something quite simple.
My previous experience
includes writing a single trigger in Oracle. This was somewhat easy since
everything that had to do with the trigger
happened in the trigger creation
process. In PGSQL it seems a little different.
Scenerio:
1)
Insert a row into one table
2) Fire Trigger AFTER this insert to obtain the
Primary Key value (in this case a serial8) of the row
entered.
(according to the rules,
this row should not be visible)
3) run a SELECT query against a different
table to obtain another Primary Key value (serial8) WHERE point '(X,Y)' @
bounary
4) INSERT INTO yet a third table the two primary key values obtained
from above.
Table
1:ltg
----------
ID--serial8
X--bigint
Y--bigint
Table
2:rtg
-----------
ID--serial8
BOUNDARY--closed path
Table
3:ltg_in_rtg
-------
LTGID--BIGINT
RTGID--BIGINT
Here
is the function as I have it:
DECLARE
myltgid
ltg_in_rtg.ltgid%TYPE; -- these are BIGINT types (8 bytes)
myrtgid
ltg_in_rtg.rtgid%TYPE;
x ltg.long%TYPE;
y
ltg.lat%TYPE;
BEGIN
x :=
NEW.X;
y :=
NEW.Y;
myltgid := SELECT max(id) FROM ltg;
myrtgid := SELECT id
FROM rtg WHERE point '(x,y)' @ boundary;
INSERT INTO ltg_in_rtg (ltgid,
rtgid) VALUES (myltgid, myrtgid);
END;
I am using PGAccess to create a FUNCTION, but it
is giving me such hassles. The errors are ambiguous and no linenumbers
(even in such a short function) aren't there.
I've tried "internal" and "sql" type functions both
to no avail. What am I doing wrong?
Thanks in advance
RDB
| 
