Return Primary Key from Procedure

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

Responses

Browse pgsql-sql by date

  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