CREATE OR REPLACE FUNCTION createpar() RETURNS TRIGGER
AS
$BODY$
DECLARE
partition_date TEXT;
PARTITION TEXT;
-- fileformat TEXT;
BEGIN
--
partition_date := TO_CHAR(NEW."CreatedTime", 'YYYY_WW');
PARTITION := TG_TABLE_NAME || '_' ||partition_date;
IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=PARTITION)
THEN
RAISE NOTICE 'Creating partition: %',PARTITION;
-- Should add correct week check
EXECUTE 'CREATE TABLE ' || PARTITION || ' (check (to_char("CreatedTime", ''YYYY_WW'') = ''' || partition_date || ''')) INHERITS (' || TG_TABLE_NAME || ');';
END IF;
point :=NEW."Point";
EXECUTE 'INSERT INTO ' || PARTITION || '(id, "Speed", "CreatedTime", "Course", "Altitude", "Sat", "Point", "Car_id", "SensorData" ) VALUES (' ||
NEW."id" ||','||
NEW."Speed" || ',''' ||
NEW."CreatedTime"|| ''',' ||
NEW."Course"||','||
NEW."Altitude" || ','||
NEW."Sat" || ',' ||
' ''' || point || '''::geometry' ||','||
NEW."Car_id" || ',''' ||
NEW."SensorData" ||
''') RETURNING id;';
-- INSERT INTO partition VALUES(NEW.*);
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Comments
0 B
|👍
/👎
0 B
|👍
/👎