如何查询一个名称是根据PLpgSQL函数结果构建的表

发布于04月08日

我下面有一个函数，它接受一个日期时间并返回一个整数.

CREATE OR REPLACE FUNCTION public.get_partition_index(datetime timestamp with time zone)
 RETURNS integer
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
DECLARE
    partitions integer := 52;
    datetime_utc timestamp := datetime AT TIME ZONE 'UTC';
    week_of_year integer := DATE_PART('week', datetime_utc);
    partition_index integer := week_of_year % partitions;
BEGIN
    RETURN partition_index;
END;
$function$

数据库中有52个表，名称为metric_event_1，metric_event_2，...，metric_event_10，...和metric_event_51.

现在我想根据get_partition_index函数的结果查询表.

select * from `metric_event_+billing_event_partition_index_new(now())` limit 10;

但是上面的查询返回错误:

ERROR:  syntax error at or near "`"
LINE 1: select * from `billing_events_+billing_event_partition_index...

推荐答案

更好的功能(仍然不使用！)

一个简单的SQL版本的函数可以像这样工作:

CREATE OR REPLACE FUNCTION public.get_partition_index(datetime timestamptz)
  RETURNS numeric
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
SELECT EXTRACT(week FROM $1 AT TIME ZONE 'UTC') % 52;
$func$;

这就修正了语法并改进了技术细节.整个 idea 在多个层面上仍然是无稽之谈.

无意义水平

首先，names metric_event_1…metric_event_51张桌子，是51张桌子，不是你说的52张.实际上有53 ISO weeks个.EXTRACT相应地返回从1到53的周数.你的% 52分会搞得一团糟包括一周的"0"...

您try 使用反勾引发了语法错误，因为反勾根本不用于Postgr(或标准SQL)中的引号.参见:

Insert text with single quotes in PostgreSQL

但这种try 在更深层次上是无稽之谈.不能参数化/插值标识符(包括表名)在纯SQL中.您可以在客户端中连接查询字符串，或者使用动态SQL:

CREATE OR REPLACE FUNCTION public.get_rows_from_metric_event(_tstz timestamptz, _limit int = 10)
  RETURNS SETOF metric_event  -- actual table name!
  LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
BEGIN
-- RAISE NOTICE '%',  -- to debug
   RETURN QUERY EXECUTE
   format('SELECT * FROM metric_event_%s LIMIT %s', EXTRACT(week FROM _tstz AT TIME ZONE 'UTC') % 52, _limit);
END
$func$;

电话:

SELECT * FROM public.get_rows_from_metric_event(now());

你真正应该做的是

如果metric_event是一个正确的partitioned table—并且你有enable_partition_pruning打开(默认值)—只需用一个与分区边界完全匹配的过滤器查询父表:

SELECT *
FROM   metric_event
WHERE  weeknr = EXTRACT(week FROM timestamptz '2024-04-07 00:00+2' AT TIME ZONE 'UTC')::int  -- cast!
LIMIT  10;

假设这个基本表定义:

CREATE TABLE metric_event (weeknr int, data text) PARTITION BY LIST (weeknr);

CREATE TABLE metric_event_1 PARTITION OF metric_event FOR VALUES IN (1);
...
CREATE TABLE metric_event_53 PARTITION OF metric_event FOR VALUES IN (53);

分区修剪完成了这项工作.你会得到一个查询计划，如:

Limit  (cost=0.00..25.88 rows=6 width=36)
  ->  Seq Scan on metric_event_14 metric_event  (cost=0.00..25.88 rows=6 width=36)
        Filter: (weeknr = 14)

分区边界必须完全匹配—包括数据类型！注意，date_part()返回double precision，EXTRACT返回numeric！在我的例子中，weeknr是integer，所以转换为integer！参见:

fiddle

About `EXTRACT` / `date_part()`

EXTRACT和date_part()基本相同.(大写EXTRACT，因为它实际上是一个语法元素，而不是一个普通的函数.如果有疑问，请使用EXTRACT.The manual:

The date_part function is modeled on the traditional Ingres equivalent to the SQL-standard function extract:
[...]
For historical reasons, the date_part function returns values of type double precision. This can result in a loss of precision in certain uses. Using extract is recommended instead.

大胆强调我的.