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
