Returning PK of first insert for second insert use.

From: Peter Atkins <peter(dot)atkins(at)NXCD(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Returning PK of first insert for second insert use.
Date: 2002-07-29 17:43:43
Message-ID: 1CAD483B723BD611B0C10090274FF0685547C6@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 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

Browse pgsql-sql by date

  From Date Subject
Next Message Peter Atkins 2002-07-29 18:32:57 Returning PK of first insert for second insert use.
Previous Message Roberto Mello 2002-07-29 16:44:23 Re: RES: [SQL] RES: set DateStyle to 'SQL'