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
*