Returning PK of first insert for second insert use.

Поиск
Список
Период
Сортировка
От Peter Atkins
Тема Returning PK of first insert for second insert use.
Дата
Msg-id 1CAD483B723BD611B0C10090274FF0685547C6@NXCDMAIL
обсуждение исходный текст
Список 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


В списке pgsql-sql по дате отправления:

Предыдущее
От: Ken Corey
Дата:
Сообщение: Re: Returning PK of first insert for second insert use.
Следующее
От: Milosz Krajewski
Дата:
Сообщение: How to optimize SQL query ?