Essas funções foram inspiradas nessas aqui e evoluidas para uma precisão de microsegundos.
CREATE OR REPLACE FUNCTION ts_to_uuid_v7(timestamp_in timestamptz)
RETURNS uuid
VOLATILE
LANGUAGE plpgsql
AS $$
declare
microseconds bigint;
begin
-- Mutiplies microsecond precision by 4.096 (max possible values that fit in 12 bits as in rand_a section of UUIDv7 layout)
-- Truncate to integer to seed into the UUID
microseconds = (cast(extract(microseconds from timestamp_in)::bigint - (floor(extract(milliseconds from timestamp_in))::bigint * 1000) as double precision) * 4.096)::bigint;
-- use random v4 uuid as starting point (which has the same variant we need)
-- then overlay timestamp
-- then set version 7 and add microseconds
return encode(
set_byte(
set_byte(
overlay(uuid_send(gen_random_uuid())
placing substring(int8send(floor(extract(epoch from timestamp_in) * 1000)::bigint) from 3)
from 1 for 6
),
6, (b'0111' || (microseconds >> 8)::bit(4))::bit(8)::int
),
7, microseconds::bit(8)::int
),
'hex')::uuid;
end
$$;
-- Extract timestamp with microsecond precision from a given UUID v7
CREATE OR REPLACE FUNCTION uuid_v7_to_ts(uuid_v7 uuid)
RETURNS timestamptz
IMMUTABLE
LANGUAGE plpgsql
AS $$
DECLARE
milliseconds bigint;
microseconds int;
BEGIN
-- Extracts the first 48 bits (6 bytes) of the UUID to get the milliseconds
milliseconds := (
'x' || substring(
encode(uuid_send(uuid_v7), 'hex')
from 1 for 12
)
)::bit(48)::bigint;
-- Extract the next 12 bits representing the microseconds
microseconds := (
'x' || substring(
encode(uuid_send(uuid_v7), 'hex')
from 14 for 3
)
)::bit(12)::int / 4.096;
-- Convert milliseconds to timestamp and add microseconds parts
RETURN to_timestamp(milliseconds / 1000.0) + (microseconds * '1 microsecond'::interval);
END
$$;
-- Generate a custom UUID v7 with microsecond precision based on the server clock timestamp
CREATE OR REPLACE FUNCTION generate_uuid_v7()
RETURNS uuid
VOLATILE
LANGUAGE plpgsql
AS $$
begin
return ts_to_uuid_v7(clock_timestamp());
end
$$;
-- Generate a empty UUID v7 with microsecond precision based on the received timestamp
CREATE OR REPLACE FUNCTION beginning_uuid_v7(datetime timestamptz)
RETURNS uuid
IMMUTABLE
LANGUAGE plpgsql
AS $$
BEGIN
RETURN (
substring((ts_to_uuid_v7(datetime)::text) from 1 for 18) || '-0000-000000000000'
)::uuid;
END $$;CREATE OR REPLACE FUNCTION create_weekly_partitions_for_table(table_name text, date_of_year timestamptz)
RETURNS TABLE(partition_name text)
LANGUAGE plpgsql
AS $$
DECLARE
record RECORD;
partition_start_uuid uuid;
partition_end_uuid uuid;
partition_name text;
BEGIN
SET client_min_messages = ERROR;
FOR record IN
/*
PURPOSE:
To generate a list of all weeks within a specific year, calculating the start and end date for each one.
PROBLEM IT SOLVES:
PostgreSQL's EXTRACT(week FROM ...) function follows the ISO 8601 standard, which can cause the last few days
of a year (e.g., Dec 29, 30, 31) to belong to week 1 of the following year, and vice-versa. This query
corrects this "anomaly", ensuring that each week unambiguously belongs to a single year for grouping purposes.
STEP-BY-STEP LOGIC:
1. `days_of_the_years`: Generates a row for each day of the target year.
2. `days_with_fixed_week_number`: Normalizes the week number for the days at the start/end of the year, preventing weeks from spanning across years.
3. `days_of_the_week`: Groups the days by the normalized week to find the start (MIN) and end (MAX) date of each one.
4. `Final SELECT`: Re-numbers the weeks into a clean, sequential sequence (1 to 54) and adjusts the final date to
create a half-open interval (>= start_date and < end_date), which simplifies filtering logic in the application.
*/
with days_of_the_years as (
SELECT generate_series as value
, EXTRACT(year FROM generate_series) as year_number
, EXTRACT(week FROM generate_series) as week_number
, EXTRACT(month FROM generate_series) as month_number
from generate_series(
date_trunc('year', date_of_year) -- First day of the given year
, date_trunc('year', date_of_year) + interval '1 year' - interval '1 day' -- Last day of the given year
, interval '1 day' -- Inc by day
) AS generate_series
)
, days_with_fixed_week_number as (
select year_number
, case
when week_number in (0, 1) and month_number = 12 then 53
when week_number in (52, 53) and month_number = 1 then 0
else week_number
end as week_number
, value
from days_of_the_years
)
, days_of_the_week as (
select min(value) as start_date
, max(value) as end_date
from days_with_fixed_week_number
group by year_number
, week_number
)
select ROW_NUMBER() OVER (PARTITION BY EXTRACT(year FROM start_date) ORDER BY start_date) as week_number
, start_date
, end_date + interval '1 day' as end_date
from days_of_the_week
order by start_date
LOOP
-- Generate the partition start UUID with the first 18 characters corresponding to the week timestamp and 0s for the rest
partition_start_uuid := beginning_uuid_v7(record.start_date);
-- Create next week as upper bound for partition UUID
partition_end_uuid := beginning_uuid_v7(record.end_date);
-- Set partition name based on week number and start date
partition_name := table_name || '_' || extract(year from record.start_date)::text || 'w' || LPAD(record.week_number::text, 2, '0');
-- Create the partition using the start and end week UUID as range values
EXECUTE format(
'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
partition_name,
table_name,
partition_start_uuid,
partition_end_uuid
);
-- Returning the name of the partition
RETURN QUERY SELECT partition_name;
END LOOP;
END $$
-- Criating tree years fron now of partitions to some table with range based on UUID v7
SELECT create_weekly_partitions_for_table('my_exemple_table', generate_series)
FROM
generate_series(
date_trunc('year', current_date), -- First day of the current year
date_trunc('year', current_date) + interval '3 year' - interval '1 day', -- Last day of the current year
interval '1 year' -- Inc by year
) AS generate_series;Co-autoria: José Victor Zeller Gonçalves