Received: from nxcdmail.nxcd.com (nxcdmail.nxcd.com [65.213.43.194])
	by postgresql.org (Postfix) with ESMTP id F37E2475D8D
	for <pgsql-sql@postgresql.org>; Mon, 29 Jul 2002 13:35:29 -0400 (EDT)
Received: by NXCDMAIL with Internet Mail Service (5.5.2653.19)
	id <3S7T7VDT>; Mon, 29 Jul 2002 10:43:43 -0700
Message-ID: <1CAD483B723BD611B0C10090274FF0685547C6@NXCDMAIL>
From: Peter Atkins <peter.atkins@NXCD.com>
To: pgsql-sql@postgresql.org
Subject: Returning PK of first insert for second insert use.
Date: Mon, 29 Jul 2002 10:43:43 -0700
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2653.19)
Content-Type: text/plain;
	charset="iso-8859-1"
X-Archive-Number: 200208/29
X-Sequence-Number: 8875

All,

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.

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

        DECLARE
                -- local variables
                oid1 INTEGER;
                retval INTEGER;

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

                -- Get the oid of the row just inserted.
                GET DIAGNOSTICS oid1 = RESULT_OID;

                retval := oid1;

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


Any help would be great! 

Thanks Again,
-p