Why can't I seem to be able to create a view dynamically?

Questions / Help
,
1

I’m using Ecto in a project, and I can’t seem to be able to create a view at runtime. If I use:

Repo.query("CREATE VIEW $1 AS SELECT * FROM tasks WHERE resource_type = $2 AND task_type = $3 ", [view_name, resource_type, task_type])

I get a syntax error. Here’s the full log:

[debug] QUERY ERROR db=0.5ms CREATE VIEW $1 AS SELECT * FROM tasks WHERE resource_type = $2 AND task_type = $3 ["ff4cb64a-e330-4f7e-a8f2-b6205f11f4c9", "document_report", "process_report"] {:error, %Postgrex.Error{ connection_id: 53258, message: nil, postgres: %{ code: :syntax_error, file: "scan.l", line: "1087", message: "syntax error at or near \"$1\"", pg_code: "42601", position: "13", routine: "scanner_yyerror", severity: "ERROR", unknown: "ERROR" } }}

If I don’t use the params array and just interpolate directly into the query’s text, it works. What am I doing wrong?

Thanks for any help!

2

CREATE statements cannot be prepared statements, you need to use string interpolation to build the query string or built the query otherwise to not use a prepared statement.

2 Likes
3

would prepare: :unnamed help in this case?

1 Like