Мигрировать с Mysql на postgresql
Прошу помочь с переносом базы данных с mysql на postgres. Подскажите есть ли быстрый такой способ через программы или сервисы? Мне нужно перенести все таблицы, процедуры и функции с mysql, чтобы работали на postgres. Дело в том, что сама программа использует процедуры
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetLoansHistory`(IN PUserId int)
BEGIN
DECLARE PositionId int(11);
SET PositionId = (SELECT
u.positionId
FROM users u
WHERE u.id = PUserId);
CASE
-- Если КП
WHEN PositionId IN (8, 14) THEN SELECT
l.id,
f.name AS filialName,
r.Name AS typeName,
l.startDateTime,
l.companyName,
l.BIN,
l.loanNumber,
s.name AS statusName,
l.comments,
l.summ,
lastUser.name AS lastUser
FROM loanrequest l
INNER JOIN requesttype r
ON l.typeId = r.Id
INNER JOIN statuses s
ON l.statusId = s.id
INNER JOIN segments s1
ON l.segmentId = s1.id
INNER JOIN filials f
ON l.filialId = f.id
LEFT JOIN (SELECT
l.loanId,
l.userId
FROM loanusers l
WHERE l.isFinished = 0
GROUP BY l.loanId,
l.userId) AS lastLu
ON lastLu.loanId = l.id
LEFT JOIN users lastUser
ON lastUser.id = lastLu.userId
WHERE ((l.typeId = 3
AND l.startDateTime > CURDATE())
OR (l.typeId NOT IN (3)
AND l.statusId NOT IN (13, 41)))
AND l.filialId IN (SELECT
u.filialId
FROM users u
WHERE u.id = PUserId)
ORDER BY l.id DESC;
WHEN PositionId = 3 THEN SELECT
l.id,
f.name AS filialName,
r.Name AS typeName,
l.startDateTime,
l.companyName,
l.BIN,
l.loanNumber,
s.name AS statusName,
l.comments,
l.summ,
lastUser.name AS lastUser
FROM loanrequest l
INNER JOIN requesttype r
ON l.typeId = r.Id
INNER JOIN statuses s
ON l.statusId = s.id
INNER JOIN segments s1
ON l.segmentId = s1.id
INNER JOIN filials f
ON l.filialId = f.id
LEFT JOIN (SELECT
l.loanId,
l.userId
FROM loanusers l
WHERE l.isFinished = 0
GROUP BY l.loanId,
l.userId) AS lastLu
ON lastLu.loanId = l.id
LEFT JOIN users lastUser
ON lastUser.id = lastLu.userId
WHERE (l.typeId NOT IN (3)
AND (l.statusId NOT IN (13, 13, 33, 41)
OR PUserId IN (l.ka2Id, l.checkerId, l.ka1Id))) -- (l.typeId = 3 and l.startDateTime > CURDATE()) OR -- AND l.statusId NOT IN (13,27,13,33,41)
AND l.filialId IN (SELECT
u.filialId
FROM users u
WHERE u.id = PUserId)
ORDER BY l.id DESC;
WHEN PositionId = 7 THEN SELECT
l.id,
f.name AS filialName,
r.Name AS typeName,
l.startDateTime,
l.companyName,
l.BIN,
l.loanNumber,
s.name AS statusName,
l.comments,
l.summ,
lastUser.name AS lastUser
FROM loanrequest l
INNER JOIN requesttype r
ON l.typeId = r.Id
INNER JOIN statuses s
ON l.statusId = s.id
INNER JOIN segments s1
ON l.segmentId = s1.id
INNER JOIN filials f
ON l.filialId = f.id
LEFT JOIN (SELECT
l.loanId,
l.userId
FROM loanusers l
WHERE l.isFinished = 0
GROUP BY l.loanId,
l.userId) AS lastLu
ON lastLu.loanId = l.id
LEFT JOIN users lastUser
ON lastUser.id = lastLu.userId
WHERE ((l.typeId IN (4, 5, 6)
AND (l.statusId NOT IN (13, 41))
OR PUserId IN (l.ka2Id, l.checkerId, l.ka1Id)))
-- AND PUserId IN (l.ka2Id, l.checkerId, l.ka1Id)
ORDER BY l.id DESC;
WHEN PositionId IN (9, 10) THEN SELECT
l.id,
f.name AS filialName,
r.Name AS typeName,
l.startDateTime,
l.companyName,
l.BIN,
l.loanNumber,
s.name AS statusName,
l.comments,
l.summ,
lastUser.name AS lastUser
FROM loanrequest l
INNER JOIN requesttype r
ON l.typeId = r.Id
INNER JOIN statuses s
ON l.statusId = s.id
INNER JOIN segments s1
ON l.segmentId = s1.id
INNER JOIN filials f
ON l.filialId = f.id
LEFT JOIN (SELECT
l.loanId,
l.userId
FROM loanusers l
WHERE l.isFinished = 0
GROUP BY l.loanId,
l.userId) AS lastLu
ON lastLu.loanId = l.id
LEFT JOIN users lastUser
ON lastUser.id = lastLu.userId
WHERE ((l.typeId IN (1, 2)
AND l.statusId NOT IN (13, 27, 33, 41))
OR PUserId IN (l.ka2Id, l.checkerId, l.ka1Id))
-- AND PUserId IN (l.ka2Id, l.checkerId, l.ka1Id)
ORDER BY l.id DESC;
WHEN PositionId IN (19, 21) THEN SELECT
l.id,
f.name AS filialName,
r.Name AS typeName,
l.startDateTime,
l.companyName,
l.BIN,
l.loanNumber,
s.name AS statusName,
l.comments,
l.summ,
lastUser.name AS lastUser
FROM loanrequest l
INNER JOIN requesttype r
ON l.typeId = r.Id
INNER JOIN statuses s
ON l.statusId = s.id
INNER JOIN segments s1
ON l.segmentId = s1.id
INNER JOIN filials f
ON l.filialId = f.id
LEFT JOIN (SELECT
l.loanId,
l.userId
FROM loanusers l
WHERE l.isFinished = 0
GROUP BY l.loanId,
l.userId) AS lastLu
ON lastLu.loanId = l.id
LEFT JOIN users lastUser
ON lastUser.id = lastLu.userId
INNER JOIN kbresponsibleusers k
ON k.bin = l.bin
AND k.userType = 1
WHERE l.startDateTime > CURDATE()
ORDER BY l.id DESC;
WHEN PositionId IN (20, 22) THEN SELECT
l.id,
f.name AS filialName,
r.Name AS typeName,
l.startDateTime,
l.companyName,
l.BIN,
l.loanNumber,
s.name AS statusName,
l.comments,
l.summ,
lastUser.name AS lastUser
FROM loanrequest l
INNER JOIN requesttype r
ON l.typeId = r.Id
INNER JOIN statuses s
ON l.statusId = s.id
INNER JOIN segments s1
ON l.segmentId = s1.id
INNER JOIN filials f
ON l.filialId = f.id
LEFT JOIN (SELECT
l.loanId,
l.userId
FROM loanusers l
WHERE l.isFinished = 0
GROUP BY l.loanId,
l.userId) AS lastLu
ON lastLu.loanId = l.id
LEFT JOIN users lastUser
ON lastUser.id = lastLu.userId
INNER JOIN kbresponsibleusers k
ON k.bin = l.bin
AND k.userType = 2
WHERE ((l.typeId = 3
AND l.startDateTime > CURDATE())
OR (l.typeId NOT IN (3)
AND l.statusId NOT IN (13, 41)))
ORDER BY l.id DESC;
ELSE SELECT
l.id,
f.name AS filialName,
r.Name AS typeName,
l.startDateTime,
l.companyName,
l.BIN,
l.loanNumber,
s.name AS statusName,
l.comments,
l.summ,
lastUser.name AS lastUser
FROM loanrequest l
INNER JOIN requesttype r
ON l.typeId = r.Id
INNER JOIN statuses s
ON l.statusId = s.id
INNER JOIN segments s1
ON l.segmentId = s1.id
INNER JOIN filials f
ON l.filialId = f.id
LEFT JOIN (SELECT
l.loanId,
l.userId
FROM loanusers l
WHERE l.isFinished = 0
GROUP BY l.loanId,
l.userId) AS lastLu
ON lastLu.loanId = l.id
LEFT JOIN users lastUser
ON lastUser.id = lastLu.userId
WHERE ((l.typeId = 3
AND l.startDateTime > CURDATE())
OR (l.typeId NOT IN (3)
AND l.statusId NOT IN (13, 27, 13, 33, 41)))
AND PUserId IN (l.ka2Id, l.checkerId, l.ka1Id)
ORDER BY l.id DESC;
END CASE;
END
НОВОЕ
CREATE OR REPLACE FUNCTION public.getloanshistory(
puserid integer)
RETURNS TABLE(id integer, filialname character varying, typename character varying, startdatetime date, companyname text, bin text, loannumber text, statusname character varying, comments text, summ double precision, lastuser character varying)
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000
AS $BODY$
DECLARE
PositionId iNT;
BEGIN
PositionId := (SELECT u.positionId FROM users u WHERE u.id = PUserId);
CASE
-- Если КП
WHEN PositionId IN (8, 14) THEN RETURN QUERY SELECT
l.id,
f.name AS filialName,
r.Name AS typeName,
l.startDateTime,
l.companyName,
l.BIN,
l.loanNumber,
s.name AS statusName,
l.comments,
l.summ,
lastUser.name AS lastUser
FROM loanrequest l
INNER JOIN requesttype r
ON l.typeId = r.Id
INNER JOIN statuses s
ON l.statusId = s.id
INNER JOIN segments s1
ON l.segmentId = s1.id
INNER JOIN filials f
ON l.filialId = f.id
LEFT JOIN (SELECT
l.loanId,
l.userId
FROM loanusers l
WHERE l.isFinished= b'0'
GROUP BY l.loanId,
l.userId) AS lastLu
ON lastLu.loanId = l.id
LEFT JOIN users lastUser
ON lastUser.id = lastLu.userId
WHERE ((l.typeId = 3
AND l.startDateTime > current_date)
OR (l.typeId NOT IN (3)
AND l.statusId NOT IN (13, 41)))
AND l.filialId IN (SELECT
u.filialId
FROM users u
WHERE u.id = PUserId)
ORDER BY l.id DESC;
WHEN PositionId = 3 THEN RETURN QUERY SELECT
l.id,
f.name AS filialName,
r.Name AS typeName,
l.startDateTime,
l.companyName,
l.BIN,
l.loanNumber,
s.name AS statusName,
l.comments,
l.summ,
lastUser.name AS lastUser
FROM loanrequest l
INNER JOIN requesttype r
ON l.typeId = r.Id
INNER JOIN statuses s
ON l.statusId = s.id
INNER JOIN segments s1
ON l.segmentId = s1.id
INNER JOIN filials f
ON l.filialId = f.id
LEFT JOIN (SELECT
l.loanId,
l.userId
FROM loanusers l
WHERE l.isFinished = b'0'
GROUP BY l.loanId,
l.userId) AS lastLu
ON lastLu.loanId = l.id
LEFT JOIN users lastUser
ON lastUser.id = lastLu.userId
WHERE (l.typeId NOT IN (3)
AND (l.statusId NOT IN (13, 13, 33, 41)
OR PUserId IN (l.ka2Id, l.checkerId, l.ka1Id))) -- (l.typeId = 3 and l.startDateTime > CURDATE()) OR -- AND l.statusId NOT IN (13,27,13,33,41)
AND l.filialId IN (SELECT
u.filialId
FROM users u
WHERE u.id = PUserId)
ORDER BY l.id DESC;
WHEN PositionId = 7 THEN RETURN QUERY SELECT
l.id,
f.name AS filialName,
r.Name AS typeName,
l.startDateTime,
l.companyName,
l.BIN,
l.loanNumber,
s.name AS statusName,
l.comments,
l.summ,
lastUser.name AS lastUser
FROM loanrequest l
INNER JOIN requesttype r
ON l.typeId = r.Id
INNER JOIN statuses s
ON l.statusId = s.id
INNER JOIN segments s1
ON l.segmentId = s1.id
INNER JOIN filials f
ON l.filialId = f.id
LEFT JOIN (SELECT
l.loanId,
l.userId
FROM loanusers l
WHERE l.isFinished = b'0'
GROUP BY l.loanId,
l.userId) AS lastLu
ON lastLu.loanId = l.id
LEFT JOIN users lastUser
ON lastUser.id = lastLu.userId
WHERE ((l.typeId IN (4, 5, 6)
AND (l.statusId NOT IN (13, 41))
OR PUserId IN (l.ka2Id, l.checkerId, l.ka1Id)))
-- AND PUserId IN (l.ka2Id, l.checkerId, l.ka1Id)
ORDER BY l.id DESC;
WHEN PositionId IN (9, 10) THEN RETURN QUERY SELECT
l.id,
f.name AS filialName,
r.Name AS typeName,
l.startDateTime,
l.companyName,
l.BIN,
l.loanNumber,
s.name AS statusName,
l.comments,
l.summ,
lastUser.name AS lastUser
FROM loanrequest l
INNER JOIN requesttype r
ON l.typeId = r.Id
INNER JOIN statuses s
ON l.statusId = s.id
INNER JOIN segments s1
ON l.segmentId = s1.id
INNER JOIN filials f
ON l.filialId = f.id
LEFT JOIN (SELECT
l.loanId,
l.userId
FROM loanusers l
WHERE l.isFinished = b'0'
GROUP BY l.loanId,
l.userId) AS lastLu
ON lastLu.loanId = l.id
LEFT JOIN users lastUser
ON lastUser.id = lastLu.userId
WHERE ((l.typeId IN (1, 2)
AND l.statusId NOT IN (13, 27, 33, 41))
OR PUserId IN (l.ka2Id, l.checkerId, l.ka1Id))
-- AND PUserId IN (l.ka2Id, l.checkerId, l.ka1Id)
ORDER BY l.id DESC;
WHEN PositionId IN (19, 21) THEN RETURN QUERY SELECT
l.id,
f.name AS filialName,
r.Name AS typeName,
l.startDateTime,
l.companyName,
l.BIN,
l.loanNumber,
s.name AS statusName,
l.comments,
l.summ,
lastUser.name AS lastUser
FROM loanrequest l
INNER JOIN requesttype r
ON l.typeId = r.Id
INNER JOIN statuses s
ON l.statusId = s.id
INNER JOIN segments s1
ON l.segmentId = s1.id
INNER JOIN filials f
ON l.filialId = f.id
LEFT JOIN (SELECT
l.loanId,
l.userId
FROM loanusers l
WHERE l.isFinished = b'0'
GROUP BY l.loanId,
l.userId) AS lastLu
ON lastLu.loanId = l.id
LEFT JOIN users lastUser
ON lastUser.id = lastLu.userId
INNER JOIN kbresponsibleusers k
ON k.bin = l.bin
AND k.userType = 1
WHERE l.startDateTime > current_date
ORDER BY l.id DESC;
WHEN PositionId IN (20, 22) THEN RETURN QUERY SELECT
l.id,
f.name AS filialName,
r.Name AS typeName,
l.startDateTime,
l.companyName,
l.BIN,
l.loanNumber,
s.name AS statusName,
l.comments,
l.summ,
lastUser.name AS lastUser
FROM loanrequest l
INNER JOIN requesttype r
ON l.typeId = r.Id
INNER JOIN statuses s
ON l.statusId = s.id
INNER JOIN segments s1
ON l.segmentId = s1.id
INNER JOIN filials f
ON l.filialId = f.id
LEFT JOIN (SELECT
l.loanId,
l.userId
FROM loanusers l
WHERE l.isFinished= b'0'
GROUP BY l.loanId,
l.userId) AS lastLu
ON lastLu.loanId = l.id
LEFT JOIN users lastUser
ON lastUser.id = lastLu.userId
INNER JOIN kbresponsibleusers k
ON k.bin = l.bin
AND k.userType = 2
WHERE ((l.typeId = 3
AND l.startDateTime > current_date)
OR (l.typeId NOT IN (3)
AND l.statusId NOT IN (13, 41)))
ORDER BY l.id DESC;
ELSE RETURN QUERY SELECT
l.id,
f.name AS filialName,
r.Name AS typeName,
l.startDateTime,
l.companyName,
l.BIN,
l.loanNumber,
s.name AS statusName,
l.comments,
l.summ,
lastUser.name AS lastUser
FROM loanrequest l
INNER JOIN requesttype r
ON l.typeId = r.Id
INNER JOIN statuses s
ON l.statusId = s.id
INNER JOIN segments s1
ON l.segmentId = s1.id
INNER JOIN filials f
ON l.filialId = f.id
LEFT JOIN (SELECT
l.loanId,
l.userId
FROM loanusers l
WHERE l.isFinished = b'0'
GROUP BY l.loanId,
l.userId) AS lastLu
ON lastLu.loanId = l.id
LEFT JOIN users lastUser
ON lastUser.id = lastLu.userId
WHERE ((l.typeId = 3
AND l.startDateTime > current_date)
OR (l.typeId NOT IN (3)
AND l.statusId NOT IN (13, 27, 13, 33, 41)))
AND PUserId IN (l.ka2Id, l.checkerId, l.ka1Id)
ORDER BY l.id DESC;
END CASE;
END;
$BODY$;
ALTER FUNCTION public.getloanshistory(integer)
OWNER TO postgres;
Ответы (2 шт):
Автор решения: Akina
→ Ссылка
Пример конвертации процедуры MySQL в функцию PostgreSQL. Я постарался сделать её похожей по используемым конструкциям.
Исходная MySQL функция:
CREATE PROCEDURE select_partial (param1 INT, param2 INT)
BEGIN
DECLARE min_value INT;
DECLARE max_value INT;
SET min_value = (SELECT MIN(id) FROM test WHERE val = param1);
SET max_value = (SELECT MAX(id) FROM test WHERE val = param1);
CASE WHEN param2 IN (1,3)
THEN SELECT * FROM test WHERE id < max_value;
WHEN param2 = 2
THEN SELECT * FROM test WHERE id >= min_value;
ELSE SELECT * FROM test WHERE id BETWEEN min_value AND max_value;
END CASE;
END
Аналогичная PostgreSQL функция:
CREATE FUNCTION select_partial (param1 integer, param2 integer)
RETURNS TABLE (id INT, val INT)
AS $$
DECLARE
min_value INT;
max_value INT;
BEGIN
min_value := (SELECT MIN(test.id) FROM test WHERE test.val = param1);
max_value := (SELECT MAX(test.id) FROM test WHERE test.val = param1);
CASE WHEN param2 IN (1,3)
THEN RETURN QUERY SELECT * FROM test WHERE test.id < max_value;
WHEN param2 = 2
THEN RETURN QUERY SELECT * FROM test WHERE test.id >= min_value;
ELSE RETURN QUERY SELECT * FROM test WHERE test.id BETWEEN min_value AND max_value;
END CASE;
END;
$$ LANGUAGE plpgsql;
Автор решения: Ingvar
→ Ссылка
Не в качестве рекламы, а работы для: https://www.fishcodelib.com/DBMigration.htm
Если база не слишком сложная - поможет, хотя в любом случае потребуется допиливать результат. Еще можно посоветовать EMS MySQL / PostgreSQL Manager. В части штатной работы вполне себе достойная вещь, но только под Windows ну и без конкретных косяков не обходится. И для серверов Postgre до 11 включительно, выше они не работают, ленятся.