在GROUP BY子句中使用变量

Question

我想在GROUP BY查询中使用存储过程中定义的单值变量，如下所示：

CREATE TABLE test_table
(
id uuid NOT NULL,
no int4 NOT NULL
);
CREATE TABLE bigger_table
(
id uuid NOT NULL,
no int4 NOT NULL,
count int4 NOT NULL,
time timestamp NOT NULL
);
CREATE OR REPLACE PROCEDURE test()
LANGUAGE plpgsql
AS
$$
DECLARE
v_now timestamp;
BEGIN
v_now = now();
INSERT INTO bigger_table (no, count, time)
SELECT no, COUNT(*), v_now FROM test_table
GROUP BY no;
END
$$;

这里，v_now实际上是一个常量，我知道它，但Postgres不知道，所以它希望它包含在GROUP BY子句或聚合函数中。我如何说服它在这里使用变量？

Answer 1

您是否尝试在v_now上设置别名，并在group by上使用此别名？

CREATE TABLE test_table
(
id uuid NOT NULL,
no int4 NOT NULL
);
CREATE OR REPLACE PROCEDURE test()
LANGUAGE plpgsql
AS
$$
DECLARE
v_now timestamp;
BEGIN
v_now = now();
SELECT no, COUNT(*), now() v_now FROM test_table
GROUP BY no, v_now;
END
$$;

Answer 2

Yech。人们喜欢约束。但是不能使用INSERT INTO bigger_table (no, count, time)，因为您已经定义了id uuid NOT NULL！

CREATE OR REPLACE PROCEDURE test()
LANGUAGE plpgsql
AS
$$
DECLARE
v_now timestamp;
BEGIN
v_now := now();
INSERT INTO bigger_table (id,no, count, time)
SELECT gen_random_uuid(),
no,
COUNT(*) over(partition by no ),
v_now FROM test_table;
END
$$;

• 不要使用时间戳：https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29

• now()返回带时区的时间戳
  select pg_typeof(now());返回timestamp with time zone