How to get auto increment value in prepared command in postgresql?

Question

I create table with this sql command:

create table recorddata 
( id serial, 
  name char(20),
  age int);

just like auto increment in mysql, I want id is auto increment in postgresql.

then, insert data in a transaction.sql command: begin, insert..., commit.

I use libpg.a to do this:

 first, PQexec(conn, "begin);
 then,  PQprepare(conn, "insert", "insert into recorddata (name, age) values ($1, $2)", 2, NULL)
 and,   PQexecPrepared to insert actual data
 final, PQexec(conn, "end")

But, how can i get id value for each time execute function PQexecPrepared ? I tried using SELECT nextval after execute PQexecPrepared, but i can not query in a insert transaction; and i also tried using sql command:insert into recorddata (name, age) values (&1, $2) return id, but it not work.

Use “returning” in your SQL statement, works differently but better because you can return also other values — Frank Heikens, May 07 '22 at 16:09

score 1 · Answer 1 · answered May 07 '22 at 23:45

1

It should work with: insert into recorddata (name, age) values (&1, $2) return id Just like you do some Query SQL statement which returns a result set.

answered May 07 '22 at 23:45

SeanH

538
2
8

yes, it worked. I make a mistake. When use PQresultStatus to check the return value of PQexecPrepared, I forget to change PGRES_COMMAND_OK to PGRES_TUPLES_OK – shalow May 09 '22 at 10:45

How to get auto increment value in prepared command in postgresql?

1 Answers1