pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Return Primary Key from Procedure
2+ messages / 2 participants
[nested] [flat]

* Return Primary Key from Procedure
@ 2002-07-24 14:12  Peter Atkins <peter.atkins@NXCD.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Peter Atkins @ 2002-07-24 14:12 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Return Primary Key from Procedure
@ 2002-07-25 10:46  Richard Huxton <dev@archonet.com>
  parent: Peter Atkins <peter.atkins@NXCD.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Richard Huxton @ 2002-07-25 10:46 UTC (permalink / raw)
  To: Peter Atkins <peter.atkins@NXCD.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2002-07-25 10:46 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-24 14:12 Return Primary Key from Procedure Peter Atkins <peter.atkins@NXCD.com>
2002-07-25 10:46 ` Richard Huxton <dev@archonet.com>

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