Message-ID: <63384.24.52.245.104.1034949470.squirrel@webmail.ac6rm.net>
Date: Fri, 18 Oct 2002 06:57:50 -0700 (PDT)
Subject: TRIGGERed INSERTS
From: "Martin Crundall" <martin@ac6rm.net>
To: <pgsql-sql@postgresql.org>
Importance: Normal
Reply-To: martin@ac6rm.net
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Howdy;

   I'm writing a "script" to pre-populate a database system that's
already in place.  The database system is in an advanced stage of
development and includes many stored procedures and TRIGGERS.

   The "script" is really a stored procedure designed to be executed by
the system's admin guy as part of "user system start-up."  Within the
script I'm calling stored procedures to create "items" (which in my
system are "data" table records with corresponding records in an
ordering/pointer table).

   Some of the data tables have "AFTER INSERT" TRIGGERs on them that, in
turn, insert some subordinate items into parallel data tables and the
central pointer/ordering table.

   As part of the pre-population "script" I call "add_item" and then
want to UPDATE a record in a different data table created by one of the
TRIGGERS (i.e. a "sub-record" if you will).  When I use a SELECT INTO
within the "script" to discover the pointer record key for this new
sub-record (so I can go UPDATE it), the SELECT INTO comes up with NULL.
After my "script" is done, however, a SELECT from the psql command
line discovers the pointer record's key value with no problem!

   Am I not waiting long enough within my "script" for the engine to
settle out?  Are the results of that transaction not available to me
until I roll out of the transaction initiated by my "script" procedure?
 I'm somewhat baffled as to why the SELECT INTO is coming up NOT FOUND
within the scope of my "script" procedure.  I did a search of the
mailing lists on "TRIGGERS" but didn't turn up any clues.

   Thanks for your help!

   Martin