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(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Peter Atkins
> Sent: Tuesday, 30 July 2002 2:33 AM
> To: 'pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)org
>