How to check how many rows were deleted?

[akheron]

After executing a DELETE statement, PostgreSQL returns a command tag DELETE count, where count is the number of rows that were deleted.

Is there any way to get count with purescript-psotgresql-client?

[paluh]

@akheron Here you go:

https://github.com/rightfold/purescript-postgresql-client/commit/9ae93cede46bd4ba4fcb14c8729d0ef63c1feae6

[paluh]

P.S. I hope we invest more time soon in some proper abstraction layer for generating SQL queries from PS.

[paluh]

Oh, I'm not sure if my response is correct as it uses RETURNING. Do you think that DELETE count is supported by https://github.com/brianc/node-postgres ?

[akheron]

I was already using RETURNING, and just wondered whether there's a better way.

It seems node-postgres has result.rowCount. This test file suggests it works for INSERT, DELETE, UPDATE, SELECT and COPY: https://github.com/brianc/node-postgres/blob/884e21e1ca58b7045c94d8cef4941eb97ac50ed5/test/unit/client/result-metadata-tests.js

[akheron]

It's also documented here, although not very clearly.

[paluh]

I'm not really familiar with postgresql message protocol but it seems that we can expect command tag with single Int value (beside INSERT which also provides oid) to be returned for many commands (please check CommandComplete section for reference). As @akheron pointed out it seems that node-postgres covers this scenarios with rowCount value.

Maybe we should provide additional FFI function which returns single Int value and use internally rowCount attribute from node-postgres result. If we ever migrate away from node-postgres we can then parse command tag directly.

@rightfold @akheron What do you think?

[akheron]

Do I understand correctly that you suggest adding a new function like query or execute, that returns rowCount instead of result rows?

A few questions come to my mind:

• What's the expected o for Query i o in the case of this new function?

• Are there queries where the user could be interested in both rowCount and result rows?

[paluh]

Do I understand correctly that you suggest adding a new function like query or execute, that returns rowCount instead of result rows?

Exactly. I'm just thinking aloud here, so I'm not sure if this proposition should be treated really seriously...

What's the expected o for Query i o in the case of this new function?

I think it should be Query i Int (32 bit Int should be probably enough - I have not found docs about max value which can be returned in command tag).

Are there queries where the user could be interested in both rowCount and result rows?

For sure there are scenarios (like query in question) when we don't want to fetch any rows but get a value from resulting command tag. I think that we should handle them at first :-)

[paluh]

P.S. I'm thinking about something like: https://github.com/rightfold/purescript-postgresql-client/commit/adfb33e533e2bce08a682f224df1f15aaf25f849

[akheron]

Looks good! The docstring could be clearer, e.g.:

-- | Execute a PostgreSQL query and return its command tag value 
-- | (how many rows were affected by the query). This may be useful
-- | for example with DELETE or UPDATE queries.

[paluh]

@akheron Thanks!

@rightfold Do you want to take a look a this new addition to the API? Should I just proceed with merge?

[paluh]

@akheron There is a new release: https://discourse.purescript.org/t/new-release-of-purescript-postgresql-client/430