Re: Returning PK of first insert for second insert use.

You need to do something like this:

SELECT proj_id FROM t_proj WHERE oid=xxx;

To find value of primary key from oid.

Chris

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Peter Atkins
> Sent: Tuesday, 30 July 2002 2:33 AM
> To: 'pgsql-sql@postgresql.org'
> Subject: [SQL] Returning PK of first insert for second insert use.
> 
> 
> 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
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>