From: Richard Huxton <dev@archonet.com>
To: Peter Atkins <peter.atkins@NXCD.com>
To: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Re: Return Primary Key from Procedure
Date: Thu, 25 Jul 2002 11:46:57 +0100
Message-ID: <200207251146.57886.dev@archonet.com> (raw)
In-Reply-To: <1CAD483B723BD611B0C10090274FF0685547C5@NXCDMAIL>
References: <1CAD483B723BD611B0C10090274FF0685547C5@NXCDMAIL>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, peter.atkins@NXCD.com
Subject: Re: Return Primary Key from Procedure
In-Reply-To: <200207251146.57886.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox