Number of updated rows with LibPQ

On Wed, 2022-10-05 at 16:38 +0200, Dominique Devienne wrote:

Hi,

Is there a way to programmatically now how many rows an UPDATE did update?
I've read about [PQcmdTuples][1], but surely I shouldn't have to parse
that string, no?
For selects, I have [PQnTuples][2], but what to do on INSERT, UPDATE, DELETE?
Parse the result of PQcmdTuples myself??? If so, what's the 0 in the
INSERT below?
Is the output of PQcmdTuples "stable", i.e. "official"? Not locale dependent?

Thanks, --DD

[1]: https://www.postgresql.org/docs/14/libpq-exec.html#LIBPQ-PQCMDTUPLES
[2]: https://www.postgresql.org/docs/14/libpq-exec.html#LIBPQ-PQNTUPLES

postgres=# create table foo (v int);
CREATE TABLE
postgres=# insert into foo values (1), (2), (3);
INSERT 0 3
postgres=# update foo set v = 2*v where v = 2;
UPDATE 1
postgres=# delete from foo where v = 3;
DELETE 1
postgres=# select v from foo where v > 1;
...
(1 row)
postgres=# delete from foo;
DELETE 2
postgres=# drop table foo;
DROP TABLE
postgres=#

Yes, you have to use PQcmdTuples(), and you have to convert the string to an integer.

But don't worry: the result will *not* be "INSERT 0 5", it will be just "5", so
you won't have to parse anything.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Laurenz Albe <laurenz.albe@cybertec.at> writes:

On Wed, 2022-10-05 at 16:38 +0200, Dominique Devienne wrote:

For selects, I have [PQnTuples][2], but what to do on INSERT, UPDATE, DELETE?
Parse the result of PQcmdTuples myself??? If so, what's the 0 in the
INSERT below?

Yes, you have to use PQcmdTuples(), and you have to convert the string to an integer.
But don't worry: the result will *not* be "INSERT 0 5", it will be just "5", so
you won't have to parse anything.

Yeah, just applying atoi() or atol() to the result should be enough.

regards, tom lane

On Wed, Oct 5, 2022 at 8:17 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Laurenz Albe <laurenz.albe@cybertec.at> writes:

On Wed, 2022-10-05 at 16:38 +0200, Dominique Devienne wrote:
Yes, you have to use PQcmdTuples(), and you have to convert the string to an integer.
But don't worry: the result will *not* be "INSERT 0 5", it will be just "5"

Thanks. What's the leading 0 though, then?
I guessed it might be the number of rows returned, but it isn't, see below:

postgres=# create table foo (id serial primary key, v int);
CREATE TABLE
postgres=# insert into foo (v) values (1), (2) returning id;
id
----
1
2
(2 rows)

INSERT 0 2

Yeah, just applying atoi() or atol() to the result should be enough.

Thanks too. Since I'm in C++, I used <charconv> instead, and
discovered it can be empty something, not 0.
I guess atoi() would have hidden that distinction, and worked anyway
(returning 0).

In the same topic, I've noticed an INSERT returns PGRES_COMMAND_OK,
while an INSERT-RETURNING returns PGRES_TUPLES_OK. So there's no way
to use the status to distinguish a SELECT from anything else? A RETURNING clause
makes any statement supporting it an hybrid of a command and a query,
but then how
does one know the exact "kind" of the statement?

E.g. So how does psql show INSERT in either cases? By parsing the SQL
itself, client-side?
Or is there a libpq API on PGresult that would allow to get the type
of statement the result is from?

Thanks, --DD

Le ven. 14 oct. 2022 à 13:52, Dominique Devienne <ddevienne@gmail.com> a
écrit :

On Wed, Oct 5, 2022 at 8:17 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Laurenz Albe <laurenz.albe@cybertec.at> writes:

On Wed, 2022-10-05 at 16:38 +0200, Dominique Devienne wrote:
Yes, you have to use PQcmdTuples(), and you have to convert the string

to an integer.

But don't worry: the result will *not* be "INSERT 0 5", it will be

just "5"

Thanks. What's the leading 0 though, then?

The leading number was the OID of the inserted row if you only had one row
and if the table had OID on rows. Otherwise, it was zero. It's always 0
nowadays since you can't have OID on rows.

--
Guillaume.

On Fri, 2022-10-14 at 13:52 +0200, Dominique Devienne wrote:

On Wed, Oct 5, 2022 at 8:17 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Laurenz Albe <laurenz.albe@cybertec.at> writes:

On Wed, 2022-10-05 at 16:38 +0200, Dominique Devienne wrote:
Yes, you have to use PQcmdTuples(), and you have to convert the string to an integer.
But don't worry: the result will *not* be "INSERT 0 5", it will be just "5"

Thanks. What's the leading 0 though, then?
I guessed it might be the number of rows returned, but it isn't, see below:

postgres=# create table foo (id serial primary key, v int);
CREATE TABLE
postgres=# insert into foo (v) values (1), (2) returning id;
 id
----
  1
  2
(2 rows)

INSERT 0 2

That 0 is the OID of the newly inserted tuple.
Since there are no more tables WITH OIDS, the number is always 0, but
is left in the output for compatibility reasons.

Yeah, just applying atoi() or atol() to the result should be enough.

Thanks too. Since I'm in C++, I used <charconv> instead, and
discovered it can be empty something, not 0.
I guess atoi() would have hidden that distinction, and worked anyway
(returning 0).

In the same topic, I've noticed an INSERT returns PGRES_COMMAND_OK,
while an INSERT-RETURNING returns PGRES_TUPLES_OK. So there's no way
to use the status to distinguish a SELECT from anything else? A RETURNING clause
makes any statement supporting it an hybrid of a command and a query,
but then how
does one know the exact "kind" of the statement?

E.g. So how does psql show INSERT in either cases? By parsing the SQL
itself, client-side?
Or is there a libpq API on PGresult that would allow to get the type
of statement the result is from?

The command tag is not what you look at.

You simply check the result from an INSERT statement. If it is PGRES_TUPLES_OK,
it must have been INSERT ... RETRUNING.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Laurenz Albe wrote:

Or is there a libpq API on PGresult that would allow to get the type
of statement the result is from?

The command tag is not what you look at.

Yet that's what psql does. from PrintQueryResult():

/* if it's INSERT/UPDATE/DELETE RETURNING, also print status */
if (last || pset.show_all_results)
{
cmdstatus = PQcmdStatus(result);
if (strncmp(cmdstatus, "INSERT", 6) == 0 ||
strncmp(cmdstatus, "UPDATE", 6) == 0 ||
strncmp(cmdstatus, "DELETE", 6) == 0)
PrintQueryStatus(result, printStatusFout);
}

You simply check the result from an INSERT statement. If it is
PGRES_TUPLES_OK, it must have been INSERT ... RETRUNING.

Or a SELECT, or an UPDATE RETURNING, or a DELETE RETURNING.
The code still need to look at PQcmdStatus() to learn which kind it is,
if it needs to know this.

Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite

On Fri, Oct 14, 2022 at 4:39 PM Daniel Verite <daniel@manitou-mail.org> wrote:

Or a SELECT, or an UPDATE RETURNING, or a DELETE RETURNING.
The code still need to look at PQcmdStatus() to learn which kind it is,

Thanks Daniel. This is exactly what I needed. --DD

PS: Now I wonder whether PQcmdTuples() points to the same buffer as
PQcmdStatus(), except with an offset...