I want to use a single value variable defined in a stored procedure in a GROUP BY query as follows:

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
$$;

Here, v_now is effectively a constant and I know it, but Postgres doesn't so it wants it be either included in GROUP BY clause or in an aggregation function. How can I convince it to use the variable here?

CodePudding user response：

Have you tried to put an alias on v_now and use this alias on 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
$$;

CodePudding user response：

Yech. People love constraint. But... You cannot INSERT INTO bigger_table (no, count, time) because You already defined 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
$$;

• Don't use timestamp: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_timestamp_.28without_time_zone.29

• now() return timestamp with time zone !
  select pg_typeof(now()); return timestamp with time zone