From: | Peter Atkins <peter(dot)atkins(at)NXCD(dot)com> |
---|---|
To: | "'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Return Primary Key from Procedure |
Date: | 2002-07-24 14:12:06 |
Message-ID: | 1CAD483B723BD611B0C10090274FF0685547C5@NXCDMAIL |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
All,
I have two tables t_proj, t_task see below:
CREATE TABLE t_proj (
proj_id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (proj_id),
task_id integer(12),
user_id integer(6),
title varchar(35),
description varchar(80)
);
CREATE TABLE t_task (
task_id INT NOT NULL AUTO_INCREMENT,
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
From | Date | Subject | |
---|---|---|---|
Next Message | Leao Torre do Vale | 2002-07-24 14:36:40 | Last record |
Previous Message | ROUWEZ Stephane | 2002-07-24 11:52:40 | Problem with my query whithout double-quotes |