Re: [sqlite] Feature suggestion / requesst
May I ask whether this suggestion has been considered being added to SQlite? - Original Message - From: Clemens Ladisch To: sqlite-users@mailinglists.sqlite.org Sent: Friday, June 8, 2018, 08:25:25 Subject: [sqlite] Feature suggestion / requesst Hick Gunter wrote: >> I've encountered a feature that I think would be awesome: >> https://www.postgresql.org/docs/9.3/static/dml-returning.html >> Example: INSERT INTO blah (this, that, another) VALUES (x, y, z) RETURNING >> id; > What does this do if the INSERT creates multiple rows? It returns multiple rows. (Also useful with UPDATE, DELETE.) > What about inserts generated from trigger programs? The same as a SELECT in a trigger program. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature suggestion / requesst
On Fri, Jun 8, 2018 at 5:25 AM Rowan Worth wrote: > On 3 June 2018 at 07:28, Scott Robison wrote: > > > I've encountered a feature that I think would be awesome: > > https://www.postgresql.org/docs/9.3/static/dml-returning.html > > > > Example: INSERT INTO blah (this, that, another) VALUES (x, y, z) > RETURNING id; > > > my thoughts are just that this could greatly simplify a lot of sql > > code that currently has to prepare and execute at least two statements > > to accomplish what is conceptually an atomic task. > > > > For most use cases you only need a single query: > > if (sqlite3_exec(db, "INSERT INTO blah (this, that, another) VALUES > (x,y, z)") == SQLITE_OK) { > long id = sqlite3_last_insert_rowid(db); > ... > } {{RETURNING c1, c2, ... INTO :1, :2, ...} also exists in Oracle (nightmares, really? :) In OCI, you do an out-bind for those), and another use case is returning the LOB locator for incrementally writing the blob content (after inserting empty_blob()). The equivalent in SQLite is zeroblob(N) on insert, then sqlit3_blob_open(db, tab, col, rowid), and _write(, N), and _close(). --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature suggestion / requesst
Hick Gunter wrote: >> I've encountered a feature that I think would be awesome: >> https://www.postgresql.org/docs/9.3/static/dml-returning.html >> >> Example: INSERT INTO blah (this, that, another) VALUES (x, y, z) RETURNING >> id; > > What does this do if the INSERT creates multiple rows? It returns multiple rows. (Also useful with UPDATE, DELETE.) > What about inserts generated from trigger programs? The same as a SELECT in a trigger program. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature suggestion / requesst
On Thu, Jun 7, 2018, 9:25 PM Rowan Worth wrote: > On 3 June 2018 at 07:28, Scott Robison wrote: > > > I've encountered a feature that I think would be awesome: > > https://www.postgresql.org/docs/9.3/static/dml-returning.html > > > > Example: INSERT INTO blah (this, that, another) VALUES (x, y, z) > RETURNING > > id; > > > > > > my thoughts are just that this could greatly simplify a lot of sql > > code that currently has to prepare and execute at least two statements > > to accomplish what is conceptually an atomic task. > > > > For most use cases you only need a single query: > > if (sqlite3_exec(db, "INSERT INTO blah (this, that, another) VALUES (x, > y, z)") == SQLITE_OK) { > long id = sqlite3_last_insert_rowid(db); > ... > } Fair enough. My statement was intended as a SQL only solution that is independent of the language bindings in use. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature suggestion / requesst
Agreed. Would be good. On Fri, Jun 8, 2018 at 1:25 PM, Rowan Worth wrote: > On 3 June 2018 at 07:28, Scott Robison wrote: > > > I've encountered a feature that I think would be awesome: > > https://www.postgresql.org/docs/9.3/static/dml-returning.html > > > > Example: INSERT INTO blah (this, that, another) VALUES (x, y, z) > RETURNING > > id; > > > > > > my thoughts are just that this could greatly simplify a lot of sql > > code that currently has to prepare and execute at least two statements > > to accomplish what is conceptually an atomic task. > > > > For most use cases you only need a single query: > > if (sqlite3_exec(db, "INSERT INTO blah (this, that, another) VALUES (x, > y, z)") == SQLITE_OK) { > long id = sqlite3_last_insert_rowid(db); > ... > } > > Of course this relies on the table's primary key being a rowid alias, where > the RETURNING syntax is presumably flexible enough to support multi-column > keys and such. Although I'm not sure how you'd generate sane defaults for > such columns... > > -Rowan > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature suggestion / requesst
On 3 June 2018 at 07:28, Scott Robison wrote: > I've encountered a feature that I think would be awesome: > https://www.postgresql.org/docs/9.3/static/dml-returning.html > > Example: INSERT INTO blah (this, that, another) VALUES (x, y, z) RETURNING > id; > > my thoughts are just that this could greatly simplify a lot of sql > code that currently has to prepare and execute at least two statements > to accomplish what is conceptually an atomic task. > For most use cases you only need a single query: if (sqlite3_exec(db, "INSERT INTO blah (this, that, another) VALUES (x, y, z)") == SQLITE_OK) { long id = sqlite3_last_insert_rowid(db); ... } Of course this relies on the table's primary key being a rowid alias, where the RETURNING syntax is presumably flexible enough to support multi-column keys and such. Although I'm not sure how you'd generate sane defaults for such columns... -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Feature suggestion / requesst
Background: I never finished a degree back in the dark ages, but recently was provided an opportunity to earn a degree to go along with my experience at a really affordable price. As a result, I'm taking various classes to demonstrate my worthiness. :) Last semester I had a class that used Oracle. I still have nightmares. This semester I am taking a class that has exposed me to postgresql for the first time. I can appreciate why the SQLite teams question is WWPGD when considering new features. I've encountered a feature that I think would be awesome: https://www.postgresql.org/docs/9.3/static/dml-returning.html Example: INSERT INTO blah (this, that, another) VALUES (x, y, z) RETURNING id; It seems a very handy "single step" way (from the perspective of the SQL programmer) to "select" some data from insert / update / delete statements. I concede to anyone who thinks this isn't very light their argument, and agree it would add some amount of heft to SQLite. I suspect not much, but I also know how easy it is for people who have no idea to say that to me about my own software. I don't know. I concede to anyone else who thinks we already have ways to do this their argument, my thoughts are just that this could greatly simplify a lot of sql code that currently has to prepare and execute at least two statements to accomplish what is conceptually an atomic task. Thank you for your time. -- Scott Robison ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users