pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Insert Data and autonumeric field
3+ messages / 3 participants
[nested] [flat]
* Insert Data and autonumeric field
@ 2007-02-01 19:09 André José Guergolet <AGuergolet@compugraf.com.br>
2007-02-01 18:13 ` Re: Insert Data and autonumeric field Andrew Sullivan <ajs@crankycanuck.ca>
2007-02-01 18:32 ` Re: Insert Data and autonumeric field Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: André José Guergolet @ 2007-02-01 19:09 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Insert Data and autonumeric field
2007-02-01 19:09 Insert Data and autonumeric field André José Guergolet <AGuergolet@compugraf.com.br>
@ 2007-02-01 18:13 ` Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 0 replies; 3+ messages in thread
From: Andrew Sullivan @ 2007-02-01 18:13 UTC (permalink / raw)
To: pgsql-sql
On Thu, Feb 01, 2007 at 04:09:22PM -0300, André José Guergolet wrote:
> PROBLEM: Many clients are getting duplicated IDs.
>
> What is the best way of doing this?
Use a sequence. You can get the current value of the sequence with
SELECT currval('seqname'). No, there's not a race condition; see the
docs.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Information security isn't a technological problem. It's an economics
problem.
--Bruce Schneier
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Insert Data and autonumeric field
2007-02-01 19:09 Insert Data and autonumeric field André José Guergolet <AGuergolet@compugraf.com.br>
@ 2007-02-01 18:32 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2007-02-01 18:32 UTC (permalink / raw)
To: André José Guergolet <AGuergolet@compugraf.com.br>; +Cc: pgsql-sql
=?iso-8859-1?Q?Andr=E9_Jos=E9_Guergolet?= <AGuergolet@compugraf.com.br> writes:
> INSERT INTO table1 (date, field2, field3) VALUES (now,'value2','value3');
> SELECT last_value FROM seq_table1;
You should never ever look directly at the sequence table (except
perhaps for manual debugging purposes). I think you may have confused
this with use of the lastval() function --- currval() or sometimes
lastval() are the appropriate way to get the last-assigned value.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-02-01 19:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-01 19:09 Insert Data and autonumeric field André José Guergolet <AGuergolet@compugraf.com.br>
2007-02-01 18:13 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-02-01 18:32 ` Tom Lane <tgl@sss.pgh.pa.us>
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