Pattern match postgex result - (MatchError) no match of right hand side value: %Postgrex.Result

I am trying to do pattern matching against a postgrex result, as such:

{:ok, %Postgrex.Result{rows: [[result]]}} = Postgrex.query!(:postgrex, "SELECT COUNT(1)
      FROM pages
      WHERE url = $1;", [url])

but I get

** (Mix) Could not start application dbhandler: DBHandler.Application.start(:normal, []) returned an error: shutdown: failed to start child: DBHandler
    ** (EXIT) an exception was raised:
        ** (MatchError) no match of right hand side value: %Postgrex.Result{command: :select, columns: ["count"], rows: [[1]], num_rows: 1, connection_id: 17022, messages: []}
            (dbhandler 0.1.0) lib/dbhandler.ex:139: DBHandler.private_in_db?/1
            (dbhandler 0.1.0) lib/dbhandler.ex:70: anonymous fn/1 in DBHandler.init/1
            (elixir 1.17.3) lib/enum.ex:987: Enum."-each/2-lists^foreach/1-0-"/2
            (dbhandler 0.1.0) lib/dbhandler.ex:69: DBHandler.init/1
            (stdlib 4.3.1.6) gen_server.erl:851: :gen_server.init_it/2
            (stdlib 4.3.1.6) gen_server.erl:814: :gen_server.init_it/6
            (stdlib 4.3.1.6) proc_lib.erl:240: :proc_lib.init_p_do_apply/3

What I might be doing wrong is that I didn’t put all the elements of the map, but I don’t THINK I have to do that.
So, is there a way to do this that works, or do I have to find a way other than pattern matching?

Postgrex.query! raises a Postgrex.Error exception if anything goes wrong, so it returns an Postgrex.Result without an {:ok, ...} wrapper.

You could either remove the {:ok, ...} part on the left-hand side or switch the right-hand side to Postgrex.query (no !), depending on exactly what you want to happen if the query fails.

4 Likes

Oh yeah that was stupid, I forgot I put an exclamation mark…
Well thanks :slight_smile:

I see you are fiddling with hand written queries, you might be interested in GitHub - elixir-dbvisor/sql: Brings an extensible SQL parser and sigil to Elixir, confidently write SQL with automatic parameterized queries. so that your queries are automatically parametrized.

1 Like

Looks like an amazing alternative to ecto queries!

1 Like

My goal is not to be an alternative, but a more fundamental building block for SQL based adapters https://groups.google.com/g/elixir-ecto/c/8MOkRFAdLZc

There is a potential for making Ecto.SQL more powerful and maintainable, by reusing code across adapters.

1 Like

it does look quite interesting, I’ll try to use it, and I have currently a problem so this might fix it!

1 Like

Let me know if you are experiencing any bugs, I’m still working on the parser and a conformance test suite.

Well, I’m trying to do this:

query = ~SQL[from pages]
  |> ~SQL[where url not in #{state.lock}]
  |> ~SQL[where host not in #{state.politeness}]
  |> ~SQL"select url"

Where state.lock and state.politeness are lists of strings.
But it just waits and does nothing when I use to_sql on it…

1 Like

Thank you for reporting this bug, I’ve pushed a fix on main: Fix code interpolation · elixir-dbvisor/sql@04fe463 · GitHub

Can you please test that it’s working for you.

{:sql, github: "elixir-dbvisor/sql"}

It still doesn’t work :‍(

Weird, I tried in iex and had no problem.

âžś  sql git:(main) âś— iex -S mix
Erlang/OTP 27 [erts-15.2] [source] [64-bit] [smp:10:10] [ds:10:10:10] [async-threads:1] [jit]

Interactive Elixir (1.18.0) - press Ctrl+C to exit (type h() ENTER for help)
The database for SQL.Repo has already been created
iex(1)> import SQL
SQL
iex(2)> state = %{lock: [], politeness: []}
%{lock: [], politeness: []}
iex(3)> query = ~SQL[from pages] |> ~SQL[where url not in #{state.lock}] |> ~SQL[where host not in #{state.politeness}] |> ~SQL"select url"
select url from pages where url not in  where host not in
iex(4)> state = %{lock: ["c", "d"], politeness: ["a", "b"]}
%{lock: ["c", "d"], politeness: ["a", "b"]}
iex(5)> query = ~SQL[from pages] |> ~SQL[where url not in #{state.lock}] |> ~SQL[where host not in #{state.politeness}] |> ~SQL"select url"
select url from pages where url not in 'c' 'd' where host not in 'c' 'd'

Could it be that you don’t import SQL or can you give me a better code example then the snippet?

Or could it be that there is something else interferring?

Actually, it was exactly that, I used use instead of import…
Well now I have another error but that’s from postgrex:

** (MatchError) no match of right hand side value: {:error, %Postgrex.Error{message: nil, postgres: %{code: :syntax_error, file: "scan.l", line: "1244", message: "syntax error at or near \"$0\"", pg_code: "42601", position: "40", routine: "scanner_yyerror", severity: "ERROR", unknown: "ERROR"}, connection_id: 18126, query: "select url from pages where url not in $0 where host not in $0"}}

Thanks, this issue will take a bit longer to fix. Thanks for giving this a try!

So the postgrex thing is a real bug and not something I did wrong?