就像pointed out by @JohnH,你只是 Select 了错误的引号字符.字符串常量使用单引号'
、美元$$
或命名美元$abc$
,而双引号"
用于标识符:here's the documentation on that.
您可以嵌套命名的美元引号,这对动态SQL非常有用.
调用date_trunc()
将导致timestamp
102,因为你给它一个now()
,它输出的是timestamptz
,而不是普通的timestamp
:
Name |
Result data type |
Argument data types |
date_trunc |
interval |
text, interval |
date_trunc |
timestamp with time zone |
text, timestamp with time zone |
date_trunc |
timestamp with time zone |
text, timestamp with time zone, text |
date_trunc |
timestamp without time zone |
text, timestamp without time zone |
now |
timestamp with time zone |
|
所以你需要改变你的签名或沮丧.
By default, use text
not varchar
.大多数函数期望并返回text
,因此使用varchar可能会导致与强制转换、签名不匹配、返回类型不匹配等相关的错误.
您在动态查询中忘记了一个WHERE
子句.你也不需要将布尔—屈服不等式与!=0
进行比较.如果你担心这个不等式会导致102,而你希望以不同的方式处理,你可以添加涉及is not null
的条件.
Demo at db<>fiddle:
CREATE OR REPLACE FUNCTION my_query(part_id integer)
RETURNS TABLE (
event_at timestamptz, --timestamptz is preferable
id integer,
code text, --text is a better choice than varchar
is_test boolean
)
LANGUAGE plpgsql IMMUTABLE
AS $function$
DECLARE
query varchar := format(
$dynsql$ --double quotes replaced with named dollar quotes
SELECT date_trunc('hour', event_at),
id,
code,
is_test
FROM metric_events_%s --added missing `WHERE clause`
WHERE event_at < date_trunc('hour', NOW() - interval '1 hour')
--unnecessary `!=0` removed
$dynsql$ , part_id);
BEGIN
RAISE NOTICE 'Value: %', query;
RETURN QUERY EXECUTE query;
END $function$;
select * from my_query(1);
event_at |
id |
code |
is_test |
2024-04-08 06:00:00+00 |
1 |
code1 |
f |
2024-04-08 07:00:00+00 |
2 |
code2 |
f |
正如JohnH已经提到的,它看起来确实非常像你试图模仿partitioning, which is built-in.从版本10开始,它是声明式的:
create table metric_events (
event_at timestamptz,
id integer,
code text,
is_test boolean) partition by range(event_at);
alter table metric_events attach partition metric_events_1
for values from (now()+'-4h') to (now()+'-1h');
select tableoid::regclass,* from metric_events where event_at>now()+'-5h';
tableoid |
event_at |
id |
code |
is_test |
metric_events_1 |
2024-04-08 06:36:50.937278+00 |
1 |
code1 |
f |
metric_events_1 |
2024-04-08 07:36:50.937516+00 |
2 |
code2 |
f |
tableoid
is a system column,它总是可以告诉你一行来自哪个实际的表/分区.
您可以使用pg_partman
extension自动化分区管理.
我添加了负的间隔常数,因为timestamp(tz)
上的+
操作被假设为将interval
作为正确的操作数,而不必键入它.用-
减go 一个正常数将需要显式的::interval
强制转换,因为它接受一个interval
或另一个timestamp(tz)
作为正确的操作数.
Name |
Left arg type |
Right arg type |
Result type |
+ |
timestamp without time zone |
interval |
timestamp without time zone |
- |
timestamp without time zone |
interval |
timestamp without time zone |
- |
timestamp without time zone |
timestamp without time zone |
interval |