Feature suggestion / requesst

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Feature suggestion / requesst

Scott Robison-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature suggestion / requesst

Rowan Worth-2
On 3 June 2018 at 07:28, Scott Robison <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature suggestion / requesst

D Burgess
Agreed. Would be good.

On Fri, Jun 8, 2018 at 1:25 PM, Rowan Worth <[hidden email]> wrote:

> On 3 June 2018 at 07:28, Scott Robison <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature suggestion / requesst

Scott Robison-2
In reply to this post by Rowan Worth-2
On Thu, Jun 7, 2018, 9:25 PM Rowan Worth <[hidden email]> wrote:

> On 3 June 2018 at 07:28, Scott Robison <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Feature suggestion / requesst

Hick Gunter
In reply to this post by Scott Robison-2
>
>
>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? What about inserts generated from trigger programs?


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature suggestion / requesst

Clemens Ladisch
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Feature suggestion / requesst

Scott Robison-2
In reply to this post by Hick Gunter
On Fri, Jun 8, 2018, 12:11 AM Hick Gunter <[hidden email]> 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? What about inserts
> generated from trigger programs?


Excellent questions that I don't know the answers to, but this does not
stop me from having an opinion.

I think if multiple rows are inserted, this should return multiple rows. It
is effectively a select of the inserted data.

Triggers are external to the insert, so I would expect them to not
contribute to the returning syntax result set.

Ultimately I expect, if accepted as an enhancement to SQLite, the team
would ask WWPGD, and model the implementation on that. I am not an expert
at PG so my ideas above could be way off base. I would think DML statements
using returning could be used anywhere a select could be used making for
more expressive SQL without necessarily needing to drop to the host
language.

Just a thought that I found interesting.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature suggestion / requesst

Dominique Devienne
In reply to this post by Rowan Worth-2
On Fri, Jun 8, 2018 at 5:25 AM Rowan Worth <[hidden email]> wrote:

> On 3 June 2018 at 07:28, Scott Robison <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature suggestion / requesst

Thomas Kurz
In reply to this post by Clemens Ladisch
May I ask whether this suggestion has been considered being added to SQlite?


----- Original Message -----
From: Clemens Ladisch <[hidden email]>
To: [hidden email] <[hidden email]>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users