asterixdb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Carey <dtab...@gmail.com>
Subject Re: insert returning records
Date Sat, 10 Sep 2016 04:19:32 GMT
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")


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message