PostgreSQL "INSERT RETURNING" feature and pear db query type expectation

[DanCld]

PostgreSQL supports returning data from data manipulation queries (docs: https://www.postgresql.org/docs/current/dml-returning.html). The most interesting part is about retrieving the inserted row id, offering a race-free mechanism that is both lightweight and easy to use.

Pear DB has this concept of queries being either on the "read" or the "write" side, with distinct code paths handling responses and also a regex filter thrown in for good measure on the "read" side.

I'm not seeing any non-hacky ways myself to implement a way to handle this feature, but I suppose a discussion on the matter wouldn't hurt.

[MarkMaldaba]

Well, the whole point of Pear::DB is portability.

To implement expose this functionality via the Pear::DB API would probably be quite heavy for most engines as it would require immediately running an additional SELECT whose results may or may not be used.

However, if you mean that there might be a way to cache this result on PostgresSQL so that a follow-up SELECT avoids an extra query, then that would probably be a neat thing to implement. Sounds quite a difficult thing to do effectively, but if it is possible it might be a neat optimisation to make.

[schengawegga]

Maybe it should be not that big thing. After a quick check, it should be done in pgsql.php by modifying the simpleQuery() function. Feel free to create a pull request :-)

[DanCld]

I have created a PR for this: #46

I still wonder where the convoluted logic with that regex came from, 20+ years ago; git logs do not help with the backstory.