Re: Access to inserted rows via select in a statement

From: David Johnston <polobo(at)yahoo(dot)com>
To: Jake Stride <jake(at)stride(dot)me(dot)uk>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Access to inserted rows via select in a statement
Date: 2011-10-19 23:49:15
Message-ID: B1CA05F9-F35B-47E5-9F14-AFEF0E314C66@yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Oct 19, 2011, at 19:17, Jake Stride <jake(at)stride(dot)me(dot)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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Brett Mc Bride 2011-10-20 00:19:42 Re: Access to inserted rows via select in a statement
Previous Message Jake Stride 2011-10-19 23:17:39 Access to inserted rows via select in a statement