尝试使用Ecto Elixir运行Raw查询

Question

我用PostgreSQL编写了这样的查询。

CREATE TEMP TABLE keep AS
SELECT min(snapshot_timestamp) AS snapshot_timestamp
FROM   "andaz-rkugf"
WHERE  snapshot_timestamp <= '2018-10-31'
GROUP  BY extract(epoch FROM snapshot_timestamp)::bigint / 600
ORDER  BY 1;
CREATE INDEX ON keep (snapshot_timestamp);
ANALYZE keep;
DELETE FROM "andaz-rkugf" a
WHERE  snapshot_timestamp <= '2018-10-31'
AND    NOT EXISTS (
SELECT FROM keep k
WHERE a.snapshot_timestamp = k.snapshot_timestamp
);

DROP TABLE pg_temp.keep;

我有一个表名列表，我想在其中运行上述查询。我不能把它们都放在一起运行，因为它抛出了关于准备好的语句的错误。

但是当我按照下面写的一个接一个地做的时候。

create_keep = """
CREATE TEMP TABLE "keep" AS
SELECT min(snapshot_timestamp) AS snapshot_timestamp
FROM   "#{camera}"
WHERE  snapshot_timestamp <= '2018-10-31'
GROUP  BY extract(epoch FROM snapshot_timestamp)::bigint / 600
ORDER  BY 1;
"""
create_index = """
CREATE INDEX ON "keep" (snapshot_timestamp);
"""
analyze = """
ANALYZE "keep";
"""
delete_snaphots = """
DELETE FROM "#{camera}" a
WHERE  snapshot_timestamp <= '2018-10-31'
AND    NOT EXISTS (
SELECT FROM "keep" k
WHERE a.snapshot_timestamp = k.snapshot_timestamp
);
"""
drop_keep = """
DROP TABLE "keep";
"""
[create_keep, analyze, create_index, delete_snaphots, drop_keep]
|> Enum.each(fn query -> Ecto.Adapters.SQL.query(SnapshotsRepo, query, []) |> IO.inspect() end)

在将camera作为参数传递给编写下面代码的方法时，我得到了这样的错误

第一个查询返回此

{:ok,
%Postgrex.Result{
columns: nil,
command: :select,
connection_id: 392760,
messages: [],
num_rows: 0,
rows: nil
}}

2:

{:error,
%Postgrex.Error{
connection_id: 392773,
message: nil,
postgres: %{
code: :undefined_table,
file: "namespace.c",
line: "423",
message: "relation "keep" does not exist",
pg_code: "42P01",
routine: "RangeVarGetRelidExtended",
severity: "ERROR",
unknown: "ERROR"
},
query: nil
}}

3:

{:error,
%Postgrex.Error{
connection_id: 392796,
message: nil,
postgres: %{
code: :undefined_table,
file: "namespace.c",
line: "423",
message: "relation "keep" does not exist",
pg_code: "42P01",
routine: "RangeVarGetRelidExtended",
severity: "ERROR",
unknown: "ERROR"
},
query: nil
}}

4:

{:error,
%Postgrex.Error{
connection_id: 392770,
message: nil,
postgres: %{
code: :undefined_table,
file: "tablecmds.c",
line: "1188",
message: "table "keep" does not exist",
pg_code: "42P01",
routine: "DropErrorMsgNonExistent",
severity: "ERROR",
unknown: "ERROR"
},
query: nil
}}

所以基本上，第一个甚至没有创建一个新的表KEEP。所以所有其他人也在失败。

我还试着让它保持原样，而不是"；保持"；。

你能告诉我我做错了什么吗？

Answer 1

您需要将所有Ecto.Adapters.SQL.query()调用封装在Repo.transaction(fn -> end)中，这样您的临时表和所有其他查询都可以在同一会话中运行。临时表在每个会话结束时都会被丢弃，因此无法访问