Subject: Re: Returning PK of first insert for second insert use.
From: Ken Corey <ken.corey@atomic-interactive.com>
To: Peter Atkins <peter.atkins@NXCD.com>
Cc: "'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
In-Reply-To: <1CAD483B723BD611B0C10090274FF0685547C9@NXCDMAIL>
References: <1CAD483B723BD611B0C10090274FF0685547C9@NXCDMAIL>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 29 Jul 2002 19:59:21 +0100
Message-Id: <1027969192.2354.206.camel@kenlinux.bithub.org>
Mime-Version: 1.0

On Mon, 2002-07-29 at 19:32, Peter Atkins wrote:
> I have two tables t_proj, t_task see below:
> 
> CREATE TABLE t_proj (
> proj_id SERIAL NOT NULL,
> PRIMARY KEY (proj_id),
> task_id integer(12),
> user_id integer(6),
> title varchar(35),
> description varchar(80)
> );
> 
> CREATE TABLE t_task (
> task_id SERIAL NOT NULL,
> PRIMARY KEY (task_id),
> title varchar(35),
> description varchar(80)
> );
> 
> When I insert into t_task I need to return the task_id (PK) for that insert
> to be used for the insert into the t_proj table.
> 
> I tried using RESULT_OID but I have no idea how to obtain the true PK using
> this opague id. Below is the procedure I tried to use.

Since the primary key of the first table is a SERIAL, it's really
defined as something like this:

create table t_task (
task_id int4 not null default nextval('t_task_task_id_seq'),
...

Which means that you can predict what the next value will be, store that
in a temporary var, and then insert it into both tables...

CREATE OR REPLACE FUNCTION insertTask (varchar, varchar)
        RETURNS INTEGER AS '

        DECLARE
                -- local variables
                oid1 INTEGER;
                retval INTEGER;
		tempvar int4;

        BEGIN
		select into tempvar nextval(''t_task_task_id_seq'');

                INSERT INTO t_task (task_id, title, description) 
			VALUES (tempvar,$1, $2);

                -- Everything has passed, return id as pk
                RETURN tempvar;
        END;
' LANGUAGE 'plpgsql';

WARNING: this is not guaranteed to be the correct syntax, I didn't
create the tables and the function to test it, but I do this kind of
thing all the time in my functions.

-- 
Ken Corey  CTO  http://www.atomic-interactive.com  07720 440 731