I want to use raw SQL in my insert query (Postgres setweight and to_tsvector for a tsvector column), but there seem to be no way to achieve that, the only raw-ish insert function would be insert_all but it’s not a macro so I can’t use fragment/1 there. Do I miss something or should I just run the query directly?
2 Likes
@benwilson512 how do I use update to insert a value into a table? There is no schema, I just want to insert (or ideally upsert) values into a table with a given name and apply custom functions to one of these values. E.g.
INSERT INTO search VALUES ('someval', setweight(to_tsvector('german', "some text"), 'A'));
1 Like
You can use just raw query if you really need to: https://hexdocs.pm/ecto/Ecto.Adapters.SQL.html#query/4
I believe it’s often easier to just make a raw SQL Query when it’s one time job (actually I’m lazy and I do it through psql when using PostgreSQL 
) or if those queries are really complicated. I know I might be in minority here, but after learning all those dsl’s like Ecto, ActiveRecord, and the Django one, I still find it much easier to write raw queries by hand, and only then translate them to that particular dsl if for example my coworkers would rather have it in DSL and not raw form.
2 Likes
Well, that was my current plan but I thought I might be missing something since it’s relatively easy to use fragments when doing selects for example.
I still find it much easier to write raw queries by hand, and only then translate them to that particular dsl
IMO that’s the “idiomatic” way to do that unless you only ever learn one ORM / DSL 
2 Likes
I have a follow up question here as to how the raw queries work.
This works ($1 and $2 are replaced with their values as in the example https://hexdocs.pm/ecto/Ecto.Adapters.SQL.html#query/4 ):
Repo.query("SELECT * FROM #{@table} WHERE $1=$2",
["col_name", "some value"],
[log: true])
This does not work with syntax error at or near \"$1\", cols and vars are strings with list of column names and their values but the database seems to be getting the placeholder vars:
Repo.query("INSERT INTO #{@table} ($1) VALUES ($2)",
[cols, vals],
[log: true])
Same query without using the params but adding the variables directly to query string works fine:
Repo.query("INSERT INTO #{@table} (#{cols}) VALUES (#{vals})", [], [log: true])
Not that it’s a huge problem, but to me it looks like an inconsistency in the way Ecto handles query strings. Is there an extra step that I have to do for insert query? An explanation why is it the way it is? 
2 Likes
This is not allowed in SQL - the list of columns has to be literals in the query. There’s not much we can do about it in Ecto.
4 Likes
But why does it work for SELECT but not INSERT?
1 Like
It doesn’t work in the SELECT as you think it does 
It will simply compare two strings.
2 Likes
Thanks for the feedback, is there a github link that you can throw at me without looking too long? Or are these just different parts of code that handle parts of query string and only one of the is placeholder-aware? I’d really like to look under the hood, but not hard enough to start digging myself
Also for the sake of completeness this does work:
Repo.query("UPDATE #{@table} SET (#{cols}) = (#{vals}) WHERE id = $1",
[id],
[log: true])
Moving cols and vars to params still doesn’t though, so the values after WHERE are ok
1 Like
That is because you are dynamically building the query every time, which you should not do for many reasons that include, but are not limited to, being slower and being SIGNIFICANTLY less safe (especially if you are not explicitly managing cols and vals yourself.
The $1/$/etc… parts are being passed to an already existing and usually cached query like they are function arguments.
2 Likes
That is because you are dynamically building the query every time, which you should not do for many reasons that include, but are not limited to, being slower and being SIGNIFICANTLY less safe (especially if you are not explicitly managing cols and vals yourself.
It’s about a query that is only doable with raw SQL, the UPDATE was just an example later, originally it was something like INSERT INTO search VALUES ('someval', setweight(to_tsvector('german', "some text"), 'A'));
The $1/$/etc… parts are being passed to an already existing and usually cached query like they are function arguments.
they’re only replaced in some cases, otherwise placeholder vars are left, I tried to figure out why - them being more or less fixed queries does help to explain it. Do you happen to know where in Ecto code I should look?
1 Like
This is not done by ecto, it is done by the database. The only place that documents where query variables are allowed is the database documentation. In case of postgres, for example, the insert syntax specifies column names have to be literals, while values can be any expression (including parameters like $x).
4 Likes
Well, I should have known it, thanks for clarifying!
1 Like
