INSERT with currval

[csn]

Would there be any problem with using currval in inserts? For example:

INSERT INTO table1
(name)
values ('name_' || currval('table1_id_seq'))

This works, but in my mind it shouldn't because the insert hasn't happened yet.

[Sxooter]

I'm not sure we have enough of a picture of what you're doing to be sure how you're using currval.

Basically, currval is ONLY set when you have incremented the sequence counter already in your session.

so, THIS will work fine:

create table parent (info text, date date, id serial primary key);
NOTICE:  CREATE TABLE will create implicit sequence 'parent_id_seq' for SERIAL column 'parent.id'
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'parent_id_key' for table 'parent'
create table child (id int4 references parent, moreinfo text);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)

begin;
insert into parent (info,date) values ('information','2003-08-04');
insert into child (id, moreinfo) values (currval('parent_id_seq'),'even more information');
commit;
select * from parent, child where parent.id=child.id;
    info     |    date    | id | id |       moreinfo
-------------+------------+----+----+-----------------------
 information | 2003-08-04 |  1 |  1 | even more information
(1 row)

[csn]

I'm just using a single table. For the inserts in my first post, table1 appears as so:

id name

1 name_1
2 name_2
3 name_3
4 name_4
5 name_5
6 name_6
...

It works, but I expected currval to cause an error since I figured it'd get called before the insert.

[Sxooter]

Nope, currval gets called after the nextval('seqname') that is implicit in the id field, since that's a BEFORE trigger. If it were an after trigger, then there would likely be an error.