postgresql – 在不创建临时表的情况下加入反对数组的输出

发布时间：2020-12-13 15:55:48 所属栏目：百科来源：网络整理

导读：我在UDF中有一个查询(如下所示),它不需要()一个整数和连接数组,我已经在我的pgplsql UDF中创建了一个本地临时表,因为我知道这有用.是否可以直接在查询中使用unexst来执行连接而不必创建临时表？ CREATE OR REPLACE FUNCTION search_posts( forum_id_ INTEGER

我在UDF中有一个查询(如下所示),它不需要()一个整数和连接数组,我已经在我的pgplsql UDF中创建了一个本地临时表,因为我知道这有用.是否可以直接在查询中使用unexst来执行连接而不必创建临时表？

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}');

我想这样做的原因是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.本地临时表阻止了这一点.即,我无法复制粘贴生成的查询并逐字执行.

解决方法

Is it possible to use unnest() directly in a query to perform a join
instead of having to create a temp table?

是的,效率更高.
(除非您需要在(数据修改)CTE中无法链接在一起的多个查询中的未通过数据.)

考虑这个例子：

SELECT *
FROM   tbl t
JOIN   unnest('{10,11,12}'::int[]) AS x(tbl_id) USING (tbl_id);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!