I discovered that it's my test trigger on this table that is causing the
problem.
I've now fixed it, seems the parser needs a set of () around a SELECT
statement to allow the value of that statement to be used in another
expression.
What I've been trying to do is to find out if the NEW value a trigger
sees is actually used as part of the database, i.e. is visible to
statements inside of the trigger for the NEW INSERT. It seems that they
are not. If I've figured this out right, the 'versioning snapshot'is all
the data that a trigger will see inside of it, if it's a BEFORE trigger.
Will a guru please check my assumptions based on below?
This trigger and fuction only allows an INSERT when the number of rows
in the table is:
GREATER THAN OR EQUAL to 3
BEFORE the INSERT that triggers the FUNCTION.
So I see that as proving that the select statement in the
function does not see NEW (record) in the Usrs table?
--------------------------------------------------------------------
CREATE OR REPLACE FUNCTION test_tgr_on_usrs () RETURNS OPAQUE AS '
DECLARE var_tmp INTEGER;
BEGIN
var_tmp := (SELECT COUNT(*) FROM Usrs);
IF var_tmp < 3 THEN
RETURN NULL;
ELSE
RETURN NEW;
END IF;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER tgr_on_usrs BEFORE INSERT ON Usrs
FOR EACH ROW
EXECUTE PROCEDURE test_tgr_on_usrs();
Carpe Dancem ;-)
-----------------------------------------------------------------
Remember your friends while they are alive
-----------------------------------------------------------------
Sincerely, Dennis Gearon
