Thread: Triggers and Functions

Triggers and Functions

From
Randall Barber
Date:
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