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
