Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Peter Atkins <peter.atkins@NXCD.com>,
	"'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
Subject: Re: Return Primary Key from Procedure
Date: Thu, 25 Jul 2002 11:46:57 +0100
User-Agent: KMail/1.4.1
References: <1CAD483B723BD611B0C10090274FF0685547C5@NXCDMAIL>
In-Reply-To: <1CAD483B723BD611B0C10090274FF0685547C5@NXCDMAIL>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200207251146.57886.dev@archonet.com>

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 i=
n=20
PostgreSQL. Likewise things like integer(12) - see online manual for detail=
s=20
on types and SERIAL columns.

> When I insert into t_task I need to return the task_id (PK) for that inse=
rt
> 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 usi=
ng
> 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 ty=
pe,=20
but I'd recommend an explicit SEQUENCE.

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

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

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

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

- Richard Huxton