Skip site navigation (1) Skip section navigation (2)

Re: Getting the OID of inserted row in a rule

From: Bradley Kieser <brad(at)kieser(dot)net>
To: Michael Fuhr <mike(at)fuhr(dot)org>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Getting the OID of inserted row in a rule
Date: 2004-09-22 15:30:06
Message-ID: 41519A7E.8050803@kieser.net (view raw or flat)
Thread:
Lists: pgsql-admin
Hi Michael,

But what if another insert happens in the mean time? Then the sequence 
would have advanced that the data will be scrambled. |Given that this 
could be a very active dataset, that situation is almost certain to occur.

Brad

Michael Fuhr wrote:

>On Wed, Sep 22, 2004 at 02:21:42PM +0100, Bradley Kieser wrote:
>  
>
>>I have a rule on a view that needs to insert into two tables. The one 
>>table has a serial ID as its unique key. The second table links to the 
>>first one in one of its columns.
>>
>>I would prefer to keep this as a rule-based solution and not have to 
>>write a function as I hope to relicate the solution across many views.
>>
>>I need to either be able to select nextval() the ID for the first table 
>>and somehow store this in the rule (but I don't see that rules support 
>>variables) or I need to somehow obtain the OID from the first insert in 
>>order to select back the ID that was assigned during the insert and pass 
>>it to the second insert (trivial to do as the second insert is then 
>>simply a select-based insert, provided that I know the OID of that first 
>>row!).
>>    
>>
>
>The second insert should be able to use currval() to get the current
>value of the sequence used in the first insert.  Here's an example:
>
>CREATE RULE v_ins AS ON INSERT TO v DO INSTEAD (
>  INSERT INTO a (xxx) VALUES (NEW.xxx);
>  INSERT INTO b (col1, col2, col3) VALUES (NEW.col1, NEW.col2, currval('a_id_seq'))
>);
>
>  
>

In response to

Responses

pgsql-admin by date

Next:From: Ray OntkoDate: 2004-09-22 15:37:41
Subject: Re: analytics
Previous:From: Michael FuhrDate: 2004-09-22 15:26:43
Subject: Re: Getting the OID of inserted row in a rule

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group