You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@asterixdb.apache.org by Steven Jacobs <sj...@ucr.edu> on 2016/09/09 19:15:04 UTC
insert returning records
As one of my needs for the bad project, I have extended the language
slightly for the insert statement. Here I am describing the change that I
have made to see if there are any concerns or changes to be made. At this
point it is only the insert that is changed (upsert didn't exist when I
made this change originally). I think it would be easy to add it to upsert
as well if desired.
I Added "return records" and "returning [fieldName]"
"return records" returns to the user all records that were inserted
"returning [fieldName]" returns only the valuse inserted for a single
[fieldName]
examples:
*query:*
create type TweetMessageTypeuuid as closed {
tweetid: uuid,
message-text: string
}
create dataset TweetMessageuuids(TweetMessageTypeuuid)
primary key tweetid autogenerated;
insert into dataset TweetMessageuuids(
[{ "message-text":"hello"}]
) return records;
*top of the plan (only the part after the commit becomes changed):*
distribute result [%0->$$6]
-- DISTRIBUTE_RESULT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
project ([$$6])
-- STREAM_PROJECT |PARTITIONED|
commit
-- COMMIT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
insert into test:TweetMessageuuids from record: %0->$$6
partitioned by [%0->$$4]
-- INSERT_DELETE |PARTITIONED|
*result:*
{ "tweetid": uuid("b2c8b24c-2b35-affd-ebcf-6a40f302c780"),
"message-text": "hello" }
query:
create type TweetMessageTypeuuid as closed {
tweetid: uuid,
message-text: string
}
create dataset TweetMessageuuids(TweetMessageTypeuuid)
primary key tweetid autogenerated;
insert into dataset TweetMessageuuids(
[{ "message-text":"hello"}]
) returning tweetid;
top of the plan (only the part after the commit becomes changed):
distribute result [%0->$$4]
-- DISTRIBUTE_RESULT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
commit
-- COMMIT |PARTITIONED|
project ([$$4])
-- STREAM_PROJECT |PARTITIONED|
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
insert into test:TweetMessageuuids from record: %0->$$7
partitioned by [%0->$$4]
-- INSERT_DELETE |PARTITIONED|
result:
uuid("b2c8b24c-b535-affd-ed4f-0dc961d27b6a")
Re: insert returning records
Posted by Steven Jacobs <sj...@ucr.edu>.
+1
I was planning on putting a description like this at the end, but forgot
before sending. Thanks!
Steven
On Friday, September 9, 2016, Mike Carey <dt...@gmail.com> wrote:
> To those who may be wondering, the purpose of this extension is to get
> back things like an auto-generated key (or the result of an
> in-insert-statement call to a non-pure function) in response to an insert.
> All relational DBMSs have some (non-standard AFAIK) hack for doing this
> that varies from vendor to vendor / system to system. We need something
> like this too, so that a client that's trying to insert some key-connected
> objects can get the info needed, efficiently, to do their work.
>
> On 9/10/16 12:45 AM, Steven Jacobs wrote:
>
> As one of my needs for the bad project, I have extended the language
> slightly for the insert statement. Here I am describing the change that I
> have made to see if there are any concerns or changes to be made. At this
> point it is only the insert that is changed (upsert didn't exist when I
> made this change originally). I think it would be easy to add it to upsert
> as well if desired.
>
> I Added "return records" and "returning [fieldName]"
> "return records" returns to the user all records that were inserted
> "returning [fieldName]" returns only the valuse inserted for a single [fieldName]
>
> examples:
>
> *query:*
>
> create type TweetMessageTypeuuid as closed {
>
> tweetid: uuid,
>
> message-text: string
>
> }
>
> create dataset TweetMessageuuids(TweetMessageTypeuuid)
>
> primary key tweetid autogenerated;
>
> insert into dataset TweetMessageuuids(
>
> [{ "message-text":"hello"}]
>
> ) return records;
>
> *top of the plan (only the part after the commit becomes changed):*
>
> distribute result [%0->$$6]
> -- DISTRIBUTE_RESULT |PARTITIONED|
> exchange
> -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
> project ([$$6])
> -- STREAM_PROJECT |PARTITIONED|
> commit
> -- COMMIT |PARTITIONED|
> exchange
> -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
> insert into test:TweetMessageuuids from record: %0->$$6 partitioned by [%0->$$4]
> -- INSERT_DELETE |PARTITIONED|
>
> *result:*
>
> { "tweetid": uuid("b2c8b24c-2b35-affd-ebcf-6a40f302c780"), "message-text": "hello" }
>
> query:
>
> create type TweetMessageTypeuuid as closed {
>
> tweetid: uuid,
>
> message-text: string
>
> }
>
> create dataset TweetMessageuuids(TweetMessageTypeuuid)
>
> primary key tweetid autogenerated;
>
> insert into dataset TweetMessageuuids(
>
> [{ "message-text":"hello"}]
>
> ) returning tweetid;
>
> top of the plan (only the part after the commit becomes changed):
>
> distribute result [%0->$$4]
> -- DISTRIBUTE_RESULT |PARTITIONED|
> exchange
> -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
> commit
> -- COMMIT |PARTITIONED|
> project ([$$4])
> -- STREAM_PROJECT |PARTITIONED|
> exchange
> -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
> insert into test:TweetMessageuuids from record: %0->$$7 partitioned by [%0->$$4]
> -- INSERT_DELETE |PARTITIONED|
>
> result:
>
> uuid("b2c8b24c-b535-affd-ed4f-0dc961d27b6a")
>
>
>
Re: insert returning records
Posted by Mike Carey <dt...@gmail.com>.
To those who may be wondering, the purpose of this extension is to get
back things like an auto-generated key (or the result of an
in-insert-statement call to a non-pure function) in response to an
insert. All relational DBMSs have some (non-standard AFAIK) hack for
doing this that varies from vendor to vendor / system to system. We
need something like this too, so that a client that's trying to insert
some key-connected objects can get the info needed, efficiently, to do
their work.
On 9/10/16 12:45 AM, Steven Jacobs wrote:
> As one of my needs for the bad project, I have extended the language
> slightly for the insert statement. Here I am describing the change
> that I have made to see if there are any concerns or changes to be
> made. At this point it is only the insert that is changed (upsert
> didn't exist when I made this change originally). I think it would be
> easy to add it to upsert as well if desired.
>
> I Added "return records" and "returning [fieldName]"
> "return records" returns to the user all records that were inserted
> "returning [fieldName]" returns only the valuse inserted for a single [fieldName]
> examples:
> *query:*
> create type TweetMessageTypeuuid as closed {
> tweetid: uuid,
> message-text: string
> }
> create dataset TweetMessageuuids(TweetMessageTypeuuid)
> primary key tweetid autogenerated;
> insert into dataset TweetMessageuuids(
> [{ "message-text":"hello"}]
> ) return records;
> *top of the plan (only the part after the commit becomes changed):*
> distribute result [%0->$$6]
> -- DISTRIBUTE_RESULT |PARTITIONED|
> exchange
> -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
> project ([$$6])
> -- STREAM_PROJECT |PARTITIONED|
> commit
> -- COMMIT |PARTITIONED|
> exchange
> -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
> insert into test:TweetMessageuuids from record: %0->$$6 partitioned by [%0->$$4]
> -- INSERT_DELETE |PARTITIONED|
> *result:*
> { "tweetid": uuid("b2c8b24c-2b35-affd-ebcf-6a40f302c780"), "message-text": "hello" }
> query:
> create type TweetMessageTypeuuid as closed {
> tweetid: uuid,
> message-text: string
> }
> create dataset TweetMessageuuids(TweetMessageTypeuuid)
> primary key tweetid autogenerated;
> insert into dataset TweetMessageuuids(
> [{ "message-text":"hello"}]
> ) returning tweetid;
> top of the plan (only the part after the commit becomes changed):
> distribute result [%0->$$4]
> -- DISTRIBUTE_RESULT |PARTITIONED|
> exchange
> -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
> commit
> -- COMMIT |PARTITIONED|
> project ([$$4])
> -- STREAM_PROJECT |PARTITIONED|
> exchange
> -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
> insert into test:TweetMessageuuids from record: %0->$$7 partitioned by [%0->$$4]
> -- INSERT_DELETE |PARTITIONED|
> result:
> uuid("b2c8b24c-b535-affd-ed4f-0dc961d27b6a")