DO
|
$BODY$
|
BEGIN
|
|
IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'aluvisa') THEN
|
CREATE USER aluvisa WITH ENCRYPTED PASSWORD 'aluvisa' LOGIN SUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION;
|
END IF;
|
|
|
IF NOT EXISTS (SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace WHERE spcname = 'ARTICHISTORICAL') THEN
|
CREATE TABLESPACE "ARTICHISTORICAL" OWNER aluvisa LOCATION 'D:\database\ARTICHISTORICAL';
|
END IF;
|
|
ALTER TABLESPACE "ARTICHISTORICAL" OWNER TO aluvisa;
|
|
IF NOT EXISTS (SELECT FROM pg_database WHERE datname = 'ARTICHISTORICAL') THEN
|
CREATE DATABASE "ARTICHISTORICAL" WITH OWNER = aluvisa TEMPLATE = 'template0' ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C' TABLESPACE = "ARTICHISTORICAL" CONNECTION LIMIT = -1;
|
END IF;
|
|
|
END
|
$BODY$;
|
|
|
|
CREATE UNLOGGED TABLE IF NOT EXISTS measurement
|
(
|
city_id int not null,
|
logdate date not null,
|
peaktemp int,
|
unitsales int
|
)
|
PARTITION BY RANGE (logdate);
|
|
|
CREATE UNLOGGED TABLE IF NOT EXISTS measurement_2018 PARTITION OF measurement FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');
|
CREATE UNLOGGED TABLE IF NOT EXISTS measurement_2019 PARTITION OF measurement FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');
|
CREATE UNLOGGED TABLE IF NOT EXISTS measurement_2020 PARTITION OF measurement FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
|
|
|
|
CREATE INDEX IF NOT EXISTS index_logdate ON measurement_2018 (logdate);
|
CREATE INDEX IF NOT EXISTS index_logdate ON measurement_2019 (logdate);
|
CREATE INDEX IF NOT EXISTS index_logdate ON measurement_2020 (logdate);
|
|
|
|
|
|
|
CREATE OR REPLACE PROCEDURE measurement_creator(in inputtimestamp bigint)
|
|
LANGUAGE 'plpgsql'
|
|
AS $BODY$
|
|
DECLARE
|
|
subtable VARCHAR(16);
|
timestamp1 VARCHAR(16);
|
timestamp2 VARCHAR(16);
|
usetimestamp bigint;
|
|
BEGIN
|
|
IF (inputtimestamp = 0) then
|
SELECT extract(epoch from now() AT TIME ZONE 'UTC') into usetimestamp::bigint;
|
ELSE
|
usetimestamp := inputtimestamp;
|
END IF;
|
|
raise notice 'Value: %', usetimestamp;
|
|
subtable := to_char(to_timestamp(usetimestamp), 'YYYY_MM');
|
timestamp1 := to_char(to_timestamp(usetimestamp), 'YYYY-MM') || '-01';
|
timestamp2 := to_char(to_timestamp(usetimestamp) + interval '1 month' , 'YYYY-MM') || '-01';
|
|
|
raise notice 'Value: %', subtable;
|
raise notice 'Value: %', timestamp1;
|
raise notice 'Value: %', timestamp2;
|
|
|
EXECUTE 'CREATE TABLE IF NOT EXISTS measurement_' || subtable || ' PARTITION OF measurement FOR VALUES FROM (' || quote_literal(timestamp1) || ') TO (' || quote_literal(timestamp2) || ')';
|
|
|
END;
|
$BODY$;
|
