PostgreSQL 9.3中的动态表名称 [英] Dynamic table name in postgreSQL 9.3

问题描述

我正在使用postgreSQL。我想从表中选择数据。该表名称包含当前年份。例如abc2013。我已经尝试过

I am using postgreSQL. I want to select data from a table. Such table name contains the current year. such as abc2013. I have tried

select * from concat('abc',date_part('year',current_date)) 

select  *from from  concat('abc', extract (year from current_date))

所以如何从这样的表中动态获取数据？

So how to fetch data from such table dynamically?

推荐答案

请不要这样做-首先从< a href = http://www.postgresql.org/docs/current/static/ddl-partitioning.html rel = nofollow>分区和约束排除。

Please don't do this - look hard at alternatives first, starting with partitioning and constraint exclusion.

如果必须使用动态表名，请在查询生成过程中在应用程序级别执行。

If you must use dynamic table names, do it at application level during query generation.

如果其他所有方法均失败，则可以使用PL / PgSQL过程像这样：

If all else fails you can use a PL/PgSQL procedure like:

CREATE OR REPLACE pleasedont(int year) RETURNS TABLE basetable AS $$
BEGIN
    RETURN QUERY EXECUTE format('SELECT col1, col2, col3 FROM %I', 'basetable_'||year);
END;
$$ LANGUAGE plpgsql;

仅当您具有与子表具有相同结构的基本表时，此方法才有效。当您开始添加限定词（where子句约束等）时，使用它也非常痛苦，并且它会阻止任何形式的计划缓存或有效的预准备语句使用。

This will only work if you have a base table that has the same structure as the sub-tables. It's also really painful to work with when you start adding qualifiers (where clause constraints, etc), and it prevents any kind of plan caching or effective prepared statement use.

这篇关于PostgreSQL 9.3中的动态表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！