针对使用 PDO 在 PostgreSQL 中抛出"must appear in the GROUP BY clause"的 JSON 密钥的预处理语句

Question

我正在尝试通过json键动态分组以找到平均答案。

select
tbl.data->>'Example' as response,
count(tbl.data->>'Example') as total
from table tbl
group by tbl.data->>'Example'
order by total
limit 1

这个查询工作得很好，当它在PostgreSQL内运行，我得到我预期的结果:

|     response   |  total  |
|--------------------------|
|  Hello World   |    4    |

问题现在发生时，我不知道的关键。它们是动态创建的，因此我需要循环遍历它们。

$sql = <<<END
select
tbl.data->>? as response,
count(tbl.data->>?) as total
from table tbl
group by tbl.data->>?
order by total
limit 1
END;
$stmt = (new PDO(...))->Prepare($sql);
$stmt->execute(array_fill(1, 3, 'Example'));
$stmt->fetch(PDO::FETCH_ASSOC);

'Example'来自用户输入。JSON是由用户创建的，键可以是任何东西。在这种情况下，它是硬编码的，但我运行一个单独的SQL查询来获取所有键并循环它们:

但是我总是得到以下错误:

台。数据必须出现在GROUP BY子句中，或者用于聚合函数

现在，我假设这是因为预处理语句将该列视为数据，但该信息来自用户输入，因此我需要使用预处理语句。

select json_object_keys(data) as keys from table

我该如何解决这个问题?

Answer 1

我不懂Php但是从这个链接(https://kb.objectrocket.com/postgresql/postgres-stored-procedure-call-in-php-1475)，似乎它是相当好的在php中使用函数。

---

CREATE OR REPLACE FUNCTION find_answer (_key text)
RETURNS json
AS $$
DECLARE
is_exists boolean;
_sql text;
_return json;
BEGIN
_sql := $sql$
SELECT
row_to_json(cte.*)
FROM (
SELECT
tbl.data ->> $1 AS response,
count(tbl.data ->> $1) AS total
FROM
tbl
GROUP BY
1
ORDER BY
total DESC
LIMIT 1) cte $sql$;
SELECT
(data[_key] IS NULL) INTO is_exists
FROM
tbl;
IF is_exists THEN
RAISE EXCEPTION '% not exists.', _key;
ELSE
RAISE NOTICE '% sql', _sql;
EXECUTE _sql
USING _key INTO _return;
RETURN _return;
END IF;
END
$$
LANGUAGE plpgsql;

然后调用它。select * from find_answer('example');

---

about Prepared statements

预处理语句仅在当前的持续时间内有效数据库会话。当会话结束时，准备好的语句为被遗忘了，所以在再次使用之前必须重新创建。这也意味着单个预处理语句不能被多个语句使用同步数据库客户端;但是，每个客户端都可以创建自己的使用自己准备好的语句。预处理语句可以手工执行使用DEALLOCATE命令清理。