[SOLVED] elixir, ecto: Cannot pass parameter to "CREATE VIEW" raw SQL query

elixir, ecto: Cannot pass parameter to "CREATE VIEW" raw SQL query

We're building an elixir application, and using ecto to connect to our database. One of the roles of the application is to create reports based on our event store. We decided to write the code for those reports in raw SQL.

I need to create a temporary view with some parameters. Consider the following code:

Ecto.Adapters.SQL.query!(
  Repo,
  "CREATE VIEW events_view AS SELECT * FROM events WHERE type = $1",
  ["hello world"]
)

# This will fail with the following error:
# (ArgumentError) parameters must be of length 0 for query [...]

The code above, with one parameter, doesn't work (apparently at ecto/postgrex level). However, removing the argument fails the query at postgres level.

Ecto.Adapters.SQL.query!(
  Repo,
  "CREATE VIEW events_view AS SELECT * FROM events WHERE type = $1",
  []
)

# And this will fails because Postgres complains about a missing parameter
# (Postgrex.Error) ERROR 42P02 (undefined_parameter) there is no parameter $1

I've used this way of passing parameters for tons of queries, including SELECT and CREATE TABLE queries. Yet, for some reason, the CREATE VIEW doesn't seem to accept arguments.

Any idea how I can fix this?

Solution

That is a PostgreSQL limitation: You can use parameters only in SELECT, INSERT, UPDATE and DELETE statements. With all other statements, you have to construct the statement by including the values in the string. Beware of SQL injection!

This limitation is not documented, but you can see this in src/backend/parser/gram.y:

PreparableStmt:
            SelectStmt
            | InsertStmt
            | UpdateStmt
            | DeleteStmt                    /* by default all are $$=$1 */
        ;