From: Peter Atkins <peter.atkins@NXCD.com>
To: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Returning PK of first insert for second insert use.
Date: Mon, 29 Jul 2002 11:32:57 -0700
Message-ID: <1CAD483B723BD611B0C10090274FF0685547C9@NXCDMAIL> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: peter.atkins@NXCD.com
Subject: Re: Returning PK of first insert for second insert use.
In-Reply-To: <1CAD483B723BD611B0C10090274FF0685547C9@NXCDMAIL>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox