wemVDcSv

1ALTER TABLE work_calendar ADD COLUMN code varchar(255);
2ALTER TABLE cpgu_mfc DROP CONSTRAINT IF EXISTS calendar_fk;
3ALTER TABLE cpgu_mfc DROP CONSTRAINT IF EXISTS calendar;
4CREATE TYPE WorkCalendarRecord AS (
5    TIMESTAMP timestamp,
6    id  BIGINT,
7    name VARCHAR(255),
8    code VARCHAR(255)
9);
10
11
12CREATE FUNCTION setUuidsForCalendars()
13  RETURNS VOID AS $$
14DECLARE
15   row_cursor CURSOR FOR SELECT timestamp, id, name, code  from work_calendar;
16   currentRecord WorkCalendarRecord;
17
18BEGIN
19  OPEN row_cursor;
20  FETCH row_cursor INTO currentRecord;
21
22  WHILE FOUND LOOP
23    UPDATE work_calendar SET code = (SELECT uuid_in(md5(random()::text || now()::text)::cstring)) WHERE CURRENT OF row_cursor;
24        FETCH row_cursor INTO currentRecord;
25  END LOOP;
26  CLOSE row_cursor;
27
28END;
29$$ LANGUAGE plpgsql;
30
31ALTER TABLE cpgu_department ALTER COLUMN code SET NOT NULL;
32
33SELECT setUuidsForCalendars();
34UPDATE work_calendar set code = 'DEFAULT_MFC_CALENDAR' WHERE name = 'Календарь работы ОИВ';
35DROP FUNCTION setUuidsForCalendars();
36DROP TYPE WorkCalendarRecord;