在不创建临时表的情况下加入数组 unnest 的输出 [英] Join against the output of an array unnest without creating a temp table
问题描述
我在 UDF 中有一个查询(如下所示),其中 unnest()
是一个整数数组并对其进行连接,因为我知道这一点,所以我在 pgplsql UDF 中创建了一个本地临时表作品.是否可以直接在查询中使用 unnest
来执行连接,而不必创建临时表?
I have a query in a UDF (shown below) which unnest()
s an array of integers and joins against it, I have created a local temp table in my pgplsql UDF since I know this works. Is it possible to use unnest
directly in a query to perform a join instead of having to create a temp table ?
CREATE OR REPLACE FUNCTION search_posts(
forum_id_ INTEGER,
query_ CHARACTER VARYING,
offset_ INTEGER DEFAULT NULL,
limit_ INTEGER DEFAULT NULL,
from_date_ TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
to_date_ TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
in_categories_ INTEGER[] DEFAULT '{}'
)
RETURNS SETOF forum_posts AS $$
DECLARE
join_string CHARACTER VARYING := ' ';
from_where_date CHARACTER VARYING := ' ';
to_where_date CHARACTER VARYING := ' ';
query_string_ CHARACTER VARYING := ' ';
offset_str_ CHARACTER VARYING := ' ';
limit_str_ CHARACTER VARYING := ' ';
BEGIN
IF NOT from_date_ IS NULL THEN
from_where_date := ' AND fp.posted_at > ''' || from_date_ || '''';
END IF;
IF NOT to_date_ IS NULL THEN
to_where_date := ' AND fp.posted_at < ''' || to_date_ || '''';
END IF;
IF NOT offset_ IS NULL THEN
offset_str_ := ' OFFSET ' || offset_;
END IF;
IF NOT limit_ IS NULL THEN
limit_str_ := ' LIMIT ' || limit_;
END IF;
IF NOT limit_ IS NULL THEN
END IF;
CREATE LOCAL TEMP TABLE un_cat(id) ON COMMIT DROP AS (select * from unnest(in_categories_)) ;
if in_categories_ != '{}' THEN
join_string := ' INNER JOIN un_cat uc ON uc.id = fp.category_id ' ;
END IF;
s
query_string_ := '
SELECT fp.*
FROM forum_posts fp' ||
join_string
||
'WHERE fp.forum_id = ' || forum_id_ || ' AND
to_tsvector(''english'',fp.post_text) @@ to_tsquery(''english'','''|| query_||''')' ||
from_where_date ||
to_where_date ||
offset_str_ ||
limit_str_
|| ';';
RAISE NOTICE '%', query_string_;
RETURN QUERY
EXECUTE query_string_;
END;
$$ LANGUAGE plpgsql;
select * from search_posts(forum_id_:=1, query_:='scout & rampage', in_categories_ := '{71}');
我想这样做的原因是 我希望能够查看查询计划而不必查看服务器日志中的自动解释输出.本地临时表可以防止这种情况.即,我无法复制粘贴结果查询并逐字执行.
The reason I want to do this is that I want to be able to to have a look at the query plan without having to resort to look at the auto explain output in the server log. A local temp table prevents this. I.e., I can't copy paste the resulting query and execute it verbatim.
推荐答案
是否可以直接在查询中使用
unnest()
来执行连接而不是必须创建一个临时表?
Is it possible to use
unnest()
directly in a query to perform a join instead of having to create a temp table?
是的,而且效率更高:
SELECT *
FROM tbl t
JOIN unnest('{10,11,12}'::int[]) AS x(tbl_id) USING (tbl_id);
这篇关于在不创建临时表的情况下加入数组 unnest 的输出的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!