Re: oid's and primary keys on insert

On Sat, 2002-08-10 at 09:09, Nigel J. Andrews wrote:
> It's sad to reply to one's own message but...

I do it daily :o(

> I'm equally not sure how you would get out of without knowing the data. The
> call to a function to combine two data values to make a unique value would seem
> to me to require that you know the data.

Heres what I am doing (sort of):

--
-- TOC Entry ID 71 (OID 2429711)
--
-- Name: "get_loc_key" () Type: FUNCTION Owner: postgres
--

CREATE FUNCTION "get_loc_key" () RETURNS integer AS 'SELECT int_value
FROM local_data WHERE descriptor=''sys_locations_key''
' LANGUAGE 'sql';

--
-- TOC Entry ID 73 (OID 2429713)
--
-- Name: "set_unique_key" () Type: FUNCTION Owner: rob
--

CREATE FUNCTION "set_unique_key" () RETURNS text AS '
DECLARE
    sequence_number    text;
    location_number    text;
    retval text;
BEGIN
    location_number := to_char(get_loc_key(),''999MI'');
    location_number := trim(both '' '' from location_number);
    sequence_number := to_char(nextval(''location_seq''),''999999999MI'');
    sequence_number := trim(both '' '' from sequence_number);
    retval := location_number || ''-'' || sequence_number;
    RETURN retval;
END;
' LANGUAGE 'plpgsql';

and set_unique_key is the default value for the primary key field.  I
could I guess not have it set as default and first call teh function
andthen insert it to the filed in the insert statement, but then it's
open to forgetfullness.  By default then it cant be left out.

> However, I would wary of using the
> return information from an insert unless I knew with certainty that there was

Is it possible for teh oid returned to be the oid from another insert?
I didn't know that.  If thats the case then none of this matters :o)


--

*
*  Rob Brown-Bayliss
*