search

raku-community-modules / DBIish

Database interface for Raku
89 stars 31 forks source link

Support default value in insert&update #214

Open baest opened 2 years ago

baest commented 2 years ago

Currently it doesn't seem there is any why of inserting or updating with a default value like:

insert into users (id, firstname, lastname) values (default, 'Arthur', 'Dent');

or

update users set id = default where ...

I can see DBD::Pg implements this with a special variable: $DBD::Pg::DBDPG_DEFAULT and handles replacing ? $\d with DEFAULT.

rbt commented 2 years ago

This is trickier than it first appears, particularly since nearly everything we do with Pg is done via a prepared statement and replacing ? or $1 with the DEFAULT keyword changes the statement which doesn't help performance for repeated executions.

Detecting the special $DEFAULT value in the parameter list would be cheap but I think execution would need to give up the few optimizations the driver does have.

It would be nice if it could be a server-side feature rather than implemented in the driver but that looks clumsy to wedge into libpq's PqExecParams() without a protocol break. At best you could set aside an unused type OID and push it through paramTypes.