Re: Returning PK of first insert for second insert use.

On Mon, 2002-07-29 at 19:32, Peter Atkins wrote:
> 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.

Since the primary key of the first table is a SERIAL, it's really
defined as something like this:

create table t_task (
task_id int4 not null default nextval('t_task_task_id_seq'),
...

Which means that you can predict what the next value will be, store that
in a temporary var, and then insert it into both tables...

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

DECLARE
-- local variables
oid1 INTEGER;
retval INTEGER;
tempvar int4;

BEGIN
select into tempvar nextval(''t_task_task_id_seq'');

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

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

WARNING: this is not guaranteed to be the correct syntax, I didn't
create the tables and the function to test it, but I do this kind of
thing all the time in my functions.

--
Ken Corey CTO http://www.atomic-interactive.com 07720 440 731