0

I'm trying to hash the current ID of every book I insert in the database, so that every book will be recognized by such 'code'.

Is there is a way in Ecto to display value of fragment("nextval('books_id_seq')") in a changeset or Ecto.insert() ?

So far all I can do is get the last id by calling it from this function :

...
  def get_currval() do
    from(b in Book,
      select: fragment("nextval('books_id_seq')"),
      limit: 1
    )
    |> Repo.one
  end

I'd love to have something more reliable, like a fragment in the insert query.

kayne
  • 349
  • 2
  • 15
  • 1
    Will you be doing the hashing in SQL or do you want to pass the new id to an Elixir function? Can you post an example of how you want to hash? – Dogbert Sep 14 '16 at 02:25
  • I'm using [hashids](http://hashids.org/), with an Elixir function then store the hashed id back to the database table – kayne Sep 14 '16 at 09:54
  • Why, getting ID after insert is not good enough? Note if you are getting ID the way you do, your method does not account for failed inserts. The only way to be correct is to read assigned ID after insert and hash it afterwards. If hashes are stored in DB you will need a transaction, to make sure that hashes created for valid IDs only. – ash Sep 14 '16 at 12:37
  • I don't see how you can call an Elixir function even if Ecto allowed fragments in inserts (which I don't think it does). The best way would be to do a transaction like @ash said. – Dogbert Sep 14 '16 at 13:37
  • Ok, and how I create this transaction that will use the returned ID, and hash it ? – kayne Sep 16 '16 at 10:50
  • Thank you guys for your suggestions, can anyone please give me an example of how to use a transaction in this case. – kayne Sep 16 '16 at 11:21

1 Answers1

0

There is a way to get ID back in the same query: RETURNING, that allows you

# \d+ tmp_play
                                                  Table "pg_temp_11.tmp_play"
 Column |         Type          |                       Modifiers                       | Storage  | Stats target | Description 
--------+-----------------------+-------------------------------------------------------+----------+--------------+-------------
 id     | integer               | not null default nextval('tmp_play_id_seq'::regclass) | plain    |              | 
 name   | character varying(32) |                                                       | extended |              | 
Indexes:
    "tmp_play_pkey" PRIMARY KEY, btree (id)

# INSERT INTO tmp_play (name) VALUES ('Hello') RETURNING id;
 id 
----
  1
(1 row)

INSERT 0 1

# INSERT INTO tmp_play (name) VALUES ('world') RETURNING id;
 id 
----
  2
(1 row)

INSERT 0 1
icuken
  • 1,306
  • 9
  • 11
  • 1
    Yes, Ecto return this ID... I'm looking for a proper way to get it before inserting, instead of inserting the record *then* update it (hashing the ID). – kayne Sep 16 '16 at 10:46
  • Now I got it, something like following? `INSERT INTO tmp_play (id, name, hash) values (nextval('tmp_play_id_seq'), 'lalala', md5(currval('tmp_play_id_seq')::text));` Or you want to do hashing inside the application? – icuken Sep 16 '16 at 11:02
  • Thanks icuken, yes I want the hashing to use an Elixir function and pattern matching (i.e; Ecto.Repo.insert(...)) – kayne Sep 16 '16 at 11:19
  • @kayne, in that case I think your only option is to do it with two queries (insert and update or select nextval() and insert with hash), otherwise you cannot predict which insert get which value from sequence and in case of concurrent inserts it easily breaks. – icuken Sep 16 '16 at 12:07