Re: oid's and primary keys on insert

Rob Brown-Bayliss wrote:
> On Fri, 2002-08-09 at 21:34, Richard Huxton wrote:
>
>
>>The standard way of doing this is with a sequence or the SERIAL type (assuming
>>you don't have a natural primary key). You can use the currval() and
>>nextval() functions to get the last used/next to use values for a given
>>sequence. It works with multiple clients and there is now an int8 based
>>version for those needing a lot of inserts.
>
>
> Unfortunatley I am not useing a sequence directly, I am useing a text
> field that is like this: 46-X
>
> The X is a sequence, the 46 is site identification number, so that when
> I replicate teh data to teh main site their is a way of knowing where it
> came from, and also ensuring that the primary key for the table is
> unique across several sites.  It is created and inserted by a trigger.
>
> So as you can see I don't actually know what the key will be before an
> insert.
>
> Currently I can then get the oid and then get the primary key for that
> table row, but if OIDs are banished then I am screwed...
>

<Sorry if this has been suggested already, or will not work for some
discussed reason -- I haven't followed this thread up to now.>

We're doing something very similar. What we did was to write a plpgsql
function that creates a primary key value as a combination of the
nextval() from a sequence and a "local site identification number" (not
what we called it, but same concept). We have the application call this
function first to get the primary key for the main header record, then
insert the header record, then reuse that value as the foreign key in
the detail records.

HTH,

Joe