Re: Return Primary Key from Procedure

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

On Wednesday 24 Jul 2002 3:12 pm, Peter Atkins wrote:
> 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)
> );

These are MySQL, not standard SQL - the AUTO_INCREMENT won't work for you in
PostgreSQL. Likewise things like integer(12) - see online manual for details
on types and SERIAL columns.

> 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.

You can get an AUTO_INCREMENT type feature by decaring task_id as SERIAL type,
but I'd recommend an explicit SEQUENCE.

richardh=> CREATE SEQUENCE foo_test_seq;
CREATE
richardh=> CREATE TABLE foo2 (task_id int4 NOT NULL DEFAULT
nextval('foo_test_seq'), a int4);
CREATE
richardh=> INSERT INTO foo2 (a) VALUES (1);
INSERT 7023473 1
richardh=> INSERT INTO foo2 (a) VALUES (2);
INSERT 7023474 1
richardh=> INSERT INTO foo2 (a) VALUES (2);
INSERT 7023475 1
richardh=> SELECT * FROM foo2;
task_id | a
---------+---
1 | 1
2 | 2
3 | 2
(3 rows)

richardh=> SELECT currval('foo_test_seq');
currval
---------
3
(1 row)

The sequence guarantees you an accurate report for this process. So - if you
have two processes each inserting, they'll only see their own "currval". Also
read up on nextval and sequences.

Sequences / serial type are more flexible than MySQL's AUTO_INCREMENT. You can
have multiple serials in a table, and share a sequence between several tables
if you want.

- Richard Huxton