daily pastebin goal
60%
SHARE
TWEET

Untitled

sergkh Oct 27th, 2017 48 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION createpar() RETURNS TRIGGER
  2. AS
  3. $BODY$
  4. DECLARE
  5.    partition_date TEXT;
  6.    PARTITION TEXT;
  7.  --  fileformat TEXT;
  8. BEGIN
  9.    --
  10.    partition_date := TO_CHAR(NEW."CreatedTime", 'YYYY_WW');
  11.    PARTITION := TG_TABLE_NAME || '_' ||partition_date;
  12.    IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=PARTITION)
  13.    THEN
  14.     RAISE NOTICE 'Creating partition: %',PARTITION;
  15.     -- Should add correct week check
  16.     EXECUTE 'CREATE TABLE ' || PARTITION || ' (check (to_char("CreatedTime", ''YYYY_WW'') = ''' || partition_date || ''')) INHERITS (' || TG_TABLE_NAME || ');';
  17.    END IF;
  18.    point :=NEW."Point";
  19.    EXECUTE 'INSERT INTO ' || PARTITION || '(id, "Speed", "CreatedTime", "Course", "Altitude", "Sat", "Point", "Car_id", "SensorData" ) VALUES (' ||
  20.    NEW."id" ||','||
  21.    NEW."Speed" || ',''' ||
  22.    NEW."CreatedTime"|| ''',' ||
  23.    NEW."Course"||','||
  24.    NEW."Altitude" || ','||
  25.    NEW."Sat" || ',' ||  
  26.     ' ''' || point || '''::geometry' ||','||
  27.    NEW."Car_id" || ','''  ||
  28.    NEW."SensorData" ||
  29. ''') RETURNING id;';
  30. --   INSERT INTO partition  VALUES(NEW.*);
  31.    RETURN NULL;
  32. END;
  33. $BODY$
  34. LANGUAGE plpgsql VOLATILE
  35. COST 100;
RAW Paste Data
Top