Postgres从动态sql字符串创建本地临时表(在提交删除时) [英] Postgres creating a local temp table (on commit drop) from a dynamic sql string

问题描述

我在postgres UDF中生成了一个查询字符串，我想将其结果放在临时表中以执行连接(我正在使用LIMIT和OFFSET，但我不想与其他ttable联接在一起最终只会在最后切断数据(例如，查询计划中的LIMIT运算符).我尝试使用以下语句创建临时表.

I have a query string generated in a postgres UDF, i'd like to put it's result in a temp table to perform joins against (I'm using LIMIT and OFFSET, and I don't want to join against other ttables only to end up choping the data off at the end --i.e., the LIMIT operator in the query plan). I attempt to create the temp table with the following statement.

CREATE LOCAL TEMP TABLE query_result ON COMMIT DROP AS EXECUTE query_string_;

但是我收到以下错误通知:

But I get the following error notice :

********** Error **********

ERROR: prepared statement "query_string_" does not exist
SQL state: 26000
Context: SQL statement "CREATE LOCAL TEMP TABLE query_result ON COMMIT DROP AS EXECUTE query_string_"
PL/pgSQL function "search_posts_unjoined" line 48 at SQL statement

此外，我尝试准备政治家，但我也无法正确使用语法.

Additionally, I tried preparing the statemen, but I couldn't get the syntax right either.

有问题的UDF是:

CREATE OR REPLACE FUNCTION search_posts_unjoined(
    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;

    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_ 
    ||  ';';

    CREATE LOCAL TEMP TABLE query_result ON COMMIT DROP AS EXECUTE query_string_;

    RAISE NOTICE '%', query_string_;

    RETURN QUERY
    EXECUTE query_string_;
END;
$$ LANGUAGE plpgsql;

并且在删除有问题的语句时有效.

And it works when the statement in question is removed.

推荐答案

改为使用:

EXECUTE '
CREATE TEMP TABLE query_result ON COMMIT DROP AS '|| query_string_;

• EXECUTE整个语句.
  语法形式CREATE TABLE foo AS EXECUTE <query>无效.

  • EXECUTE the whole statement.
    The syntax form CREATE TABLE foo AS EXECUTE <query> isn't valid.

    LOCAL只是一个干扰词，在这种情况下会被忽略.

    LOCAL is just a noise word and ignored in this context.

    更多手册中的详细信息.

    这篇关于Postgres从动态sql字符串创建本地临时表(在提交删除时)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！