Insert Data and autonumeric field

From: André José Guergolet <AGuergolet(at)compugraf(dot)com(dot)br>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Insert Data and autonumeric field
Date: 2007-02-01 19:09:22
Message-ID: E426DDFE0FB5634AA3C997254DCBDE4EDF3C91@cgexc04.compugraf.com.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi everybody,
I need to insert a row in a table and get the Id of this row ( My primary key ).

Example:

INSERT INTO table1 (date, field2, field3) VALUES (now,'value2','value3');
SELECT last_value FROM seq_table1;

I'm running each command apart. My application retrieves the last_value and uses it in another command:

INSERT INTO table2 (pk1, field1, field2, field3) VALUES ( last_value_variable, 'value1','value2','value3');

PROBLEM: Many clients are getting duplicated IDs.

What is the best way of doing this?

I tried a function:

CREATE OR REPLACE FUNCTION fu_insertrow(int4, text)
RETURNS int4 AS
$BODY$
DECLARE
i_lastvalue INTEGER;
BEGIN

INSERT INTO table1 (date, field1, field2) values (now(),$1,'$2');
SELECT i_lastvalue INTO i_lastvalue from "seq_ChamadaId";
RETURN i_lastvalue;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Using this function:

SELECT fu_insertrow(value2, value3);

The app gets the return value of the function above and uses it in my insert:
INSERT INTO table2 (pk1, field1, field2, field3) VALUES ( function_return_variable, 'value1','value2','value3');

Suggestions?

Thanks,
André Guergolet

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Wei ZOU 2007-02-01 20:56:28 Search a range of cases/records
Previous Message Andrew Sullivan 2007-02-01 18:53:16 Re: Differentiate Between Zero-Length String and NULLColumn Values