SQL PostgreSQL - 动态值作为表名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13289304/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL - dynamic value as table name
提问by Mr.
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 returns 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?
可以做到吗？任何线索如何？
回答by Craig Ringer
You will need to use the PL/PgSQL EXECUTEstatement, via a DOblock 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 函数 ( )使用PL/PgSQLEXECUTE语句。PostgreSQL 使用的普通 SQL 方言不支持动态 SQL，仅在过程 PL/PgSQL 变体中支持。DOCREATE OR REPLACE FUNCTION ... LANGUAGE plpgsql
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 %Iand %Lformat-specifiers do proper identifier and literal quoting, respectively.
的format(...)函数的%I和%L格式指定符做适当的识别符和文字分别引用。
For literals I recommend using EXECUTE ... USINGrather than format(...)with %L, but for identifiers like table/column names the format %Ipattern is a nice concise alternative to verbose quote_identcalls.
对于文字，我建议使用EXECUTE ... USING而不是format(...)with %L，但对于像表/列名称这样的标识符，格式%I模式是详细quote_ident调用的一个很好的简洁替代方案。
