pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReturning with the inserted id
2+ messages / 2 participants
[nested] [flat]
* Returning with the inserted id
@ 2005-09-02 09:09 Graf László <graf.laszlo@axis.hu>
2005-09-02 09:33 ` Re: Returning with the inserted id Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 2+ messages in thread
From: Graf László @ 2005-09-02 09:09 UTC (permalink / raw)
To: pgsql-sql
Hi all
A table was created with:
CREATE TABLE test (
id integer,
nev varchar(25),
datum timestamp
);
A sequence to hold the id was defined with:
CREATE SEQUENCE "public"."test_azon_seq"
INCREMENT 1 MINVALUE 101
MAXVALUE 9223372036854775807 START 101
CACHE 1;
The function wich allocates the id and defines the datum is:
CREATE FUNCTION test_verif() RETURNS trigger AS $test_verif$
BEGIN
select into NEW.id nextval('test_azon_seq');
NEW.datum := current_timestamp;
RETURN NEW;
END;
$test_verif$ LANGUAGE plpgsql;
and the before insert trigger is:
CREATE TRIGGER test_verif
BEFORE INSERT ON test
FOR EACH ROW
EXECUTE PROCEDURE test_verif();
When I issue an insert (see below) how can I retrieve the
inserted value of id? I need something like Oracle's returns
for insert.
insert into "public"."test" (nev) values ('text');
--
László Graf
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Returning with the inserted id
2005-09-02 09:09 Returning with the inserted id Graf László <graf.laszlo@axis.hu>
@ 2005-09-02 09:33 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Huxton @ 2005-09-02 09:33 UTC (permalink / raw)
To: Graf László <graf.laszlo@axis.hu>; +Cc: pgsql-sql
Graf László wrote:
>
> A sequence to hold the id was defined with:
> CREATE SEQUENCE "public"."test_azon_seq"
> INCREMENT 1 MINVALUE 101
> MAXVALUE 9223372036854775807 START 101
> CACHE 1;
>
> The function wich allocates the id and defines the datum is:
> CREATE FUNCTION test_verif() RETURNS trigger AS $test_verif$
> BEGIN
> select into NEW.id nextval('test_azon_seq');
> NEW.datum := current_timestamp;
> RETURN NEW;
> END;
> $test_verif$ LANGUAGE plpgsql;
I take it this is just an example, because you could do this with
DEFAULTs on both columns.
> When I issue an insert (see below) how can I retrieve the
> inserted value of id? I need something like Oracle's returns
> for insert.
>
> insert into "public"."test" (nev) values ('text');
SELECT currval('public.test_azon_seq');
And yes, it will cope with multiple concurrent connections inserting.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-09-02 09:33 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-02 09:09 Returning with the inserted id Graf László <graf.laszlo@axis.hu>
2005-09-02 09:33 ` 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