Postgres Pro
Downloads
Home   >   mailing lists

Thread: Access to inserted rows via select in a statement

Access to inserted rows via select in a statement

From
Jake Stride
Date:
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?

Thanks
Jake 

-- 
Jake Stride
Find out more http://about.me/jakestride or follow me on twitter @jake.

Re: Access to inserted rows via select in a statement

From
David Johnston
Date:
On Oct 19, 2011, at 19:17, Jake Stride <jake@stride.me.uk> wrote:

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.

David J.

Re: Access to inserted rows via select in a statement

From
Brett Mc Bride
Date:

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

 

On Oct 19, 2011, at 19:17, Jake Stride <jake@stride.me.uk> wrote:

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.

 

David J.