将Multirow子选择作为要执行的参数 [英] Multirow subselect as parameter to `execute using`

问题描述

multirow子选择将在where子句中的in运算符的右侧使用:

The multirow subselect will be used in the right hand side of the in operator in the where clause:

create table t (a integer);
insert into t (a) values (1), (9);

drop function if exists f();

create function f()
returns void as $$
begin
execute '
    select a
    from t
    where a in $1
' using (select 1 union select 2);
end;$$
language plpgsql;

select f();

ERROR:  more than one row returned by a subquery used as an expression
CONTEXT:  SQL statement "SELECT (select 1 union select 2)"
PL/pgSQL function "f" line 3 at EXECUTE statement

如果实现上述功能，如何实现?

How to achieve what the above function would if it worked?

推荐答案

在您的问题中，我看不到任何无法通过以下方式轻松解决的问题:

I see nothing in your question that couldn't more easily be solved with:

SELECT a
FROM   t
JOIN  (VALUES (1), (2)) x(a) USING (a); -- any query returning multiple int

您能否说明示例中的必要性?

Can you clarify the necessity in your example?

作为概念证明，这将更简单/更快:

As a proof of concept, this would be simpler / faster:

CREATE OR REPLACE FUNCTION x.f1()
  RETURNS SETOF integer AS
$BODY$
BEGIN
RETURN QUERY EXECUTE '
    SELECT a
    FROM t
    WHERE a = ANY($1)'
USING ARRAY(VALUES (1), (2)); -- any query here
END;
$BODY$
LANGUAGE plpgsql;

---

IN()和= ANY()的性能

您的观察到位了.这是有原因的.试试:

---

Performance of IN () and = ANY()

Your observation is spot on. And there is reason for it. Try:

EXPLAIN ANALYZE SELECT * FROM tbl WHERE id IN (1,2,3);

查询计划将显示:

索引条件:(id = ANY('{1,2,3}':: integer []))

Index Cond: (id = ANY ('{1,2,3}'::integer[]))

PostgreSQL在内部将id IN (..)构造转换为id = ANY(..).两者的性能相同-除了可忽略的间接费用损失.

PostgreSQL transforms the id IN (..) construct into id = ANY(..) internally. These two perform identically - except for a negligible overhead penalty.

我的代码只能更快地构建语句-完全按照您的诊断.

My code is only faster in building the statement - exactly as you diagnosed.

这篇关于将Multirow子选择作为要执行的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！