How about storing the transaction ID of the inserting/updating transaction in a column of your table? You could query that against the current transaction ID in your trigger function. See txid_current() in the docs (http://www.postgresql.org/docs/9.1/interactive/functions-info.html)
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of David Johnston Sent: Thursday, 20 October 2011 10:49 AM To: Jake Stride Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Access to inserted rows via select in a statement
I am writing a function that is called by a trigger after insert and update but it is not returning all the rows I want it to with a SELECT.
This trigger is called within a transaction and looks for a row inserted previously within the same transaction - it works when the trigger is called via UPDATE as the row exists from a previous insert but not the first time it is called.
Should a SELECT function within a function and a transaction be able to select other rows that have just been inserted in the transaction, but before it is committed?
No. There is no way to know before the statement is finished which individual records have been inserted to that point. If you provide the trigger and inserting code, along with your goal, alternative solutions may be doable. Prior statements should have all their inserted records visible so it may be the specific methodology you are using that is causing the problem.
Using an after statement trigger will let you see all of them though there isn't any direct way to know exactly which records were inserted.