Possible Duplicate:
Postgres Dynamic Query Function可能重复:Postgres动态查询函数
I wish to use the returned string from the query below as a table name for other query.
我希望使用下面查询返回的字符串作为其他查询的表名。
SELECT 'backup_' || TO_CHAR(CURRENT_DATE,'yyyy-mm-dd')
as you can see it return a string. I wish to use it as an input for another query, e.g.
可以看到它返回一个字符串。我希望用它作为另一个查询的输入，例如
CREATE TABLE (SELECT 'backup_' || TO_CHAR(CURRENT_DATE,'yyyy-mm-dd'))
AS * SELECT FROM backup
Can it be done? Any clue how?
可以做到吗?任何线索如何?
1 个解决方案
#1
31
You will need to use the PL/PgSQL EXECUTE statement, via a DO block or PL/PgSQL function (CREATE OR REPLACE FUNCTION ... LANGUAGE plpgsql). Dynamic SQL is not supported in the ordinary SQL dialect used by PostgreSQL, only in the procedural PL/PgSQL variant.
您将需要使用PL/PgSQL执行语句，通过一个DO块或PL/PgSQL函数(创建或替换函数……)语言plpgsql)。PostgreSQL使用的普通SQL方言不支持动态SQL，只支持过程PL/PgSQL变体。
DO
$$
BEGIN
EXECUTE format('CREATE TABLE %I AS SELECT * FROM backup', 'backup_' || to_char(CURRENT_DATE,'yyyy-mm-dd'));
END;
$$ LANGUAGE plpgsql;
The format(...) function's %I and %L format-specifiers do proper identifier and literal quoting, respectively.
format(…)函数的%I和%L格式说明符分别执行正确的标识符和文字引用。
For literals I recommend using EXECUTE ... USING rather than format(...) with %L, but for identifiers like table/column names the format %I pattern is a nice concise alternative to verbose quote_ident calls.
对于我推荐使用EXECUTE的文字…使用%L而不是format(…)，但是对于表/列名称这样的标识符，format %I模式是详细quote_ident调用的一种简洁的选择。
#1
31
You will need to use the PL/PgSQL EXECUTE statement, via a DO block or PL/PgSQL function (CREATE OR REPLACE FUNCTION ... LANGUAGE plpgsql). Dynamic SQL is not supported in the ordinary SQL dialect used by PostgreSQL, only in the procedural PL/PgSQL variant.
您将需要使用PL/PgSQL执行语句，通过一个DO块或PL/PgSQL函数(创建或替换函数……)语言plpgsql)。PostgreSQL使用的普通SQL方言不支持动态SQL，只支持过程PL/PgSQL变体。
DO
$$
BEGIN
EXECUTE format('CREATE TABLE %I AS SELECT * FROM backup', 'backup_' || to_char(CURRENT_DATE,'yyyy-mm-dd'));
END;
$$ LANGUAGE plpgsql;
The format(...) function's %I and %L format-specifiers do proper identifier and literal quoting, respectively.
format(…)函数的%I和%L格式说明符分别执行正确的标识符和文字引用。
For literals I recommend using EXECUTE ... USING rather than format(...) with %L, but for identifiers like table/column names the format %I pattern is a nice concise alternative to verbose quote_ident calls.
对于我推荐使用EXECUTE的文字…使用%L而不是format(…)，但是对于表/列名称这样的标识符，format %I模式是详细quote_ident调用的一种简洁的选择。