我正在尝试将MSSQL查询转换为POSTGRES查询。我无法在Postgres中执行以下查询
DO $$
BEGIN
IF EXISTS (SELECT ID FROM PROCESS WHERE ID = (SELECT MAX(ID) FROM PROCESS WHERE NAME = 'TRANSACTION')) THEN
SELECT * FROM MSG
WHERE msg_timestamp >= ( SELECT start_time FROM PROCESS WHERE NAME = 'TRANSACTION' AND STATUS = 'STARTED');
ELSE
SELECT * FROM MSG;
END IF;
END $$;
错误：
查询没有结果数据的目的地提示：如果要舍弃SELECT的结果，请改用PERFORM。上下文：SQL语句FCMDBPOSTGRES =＃处的PL / pgSQL函数inline_code_block第7行
我对应的MSSQL查询如下所示
IF EXISTS (SELECT ID FROM PROCESS WHERE ID = (SELECT MAX(ID) FROM PROCESS WHERE NAME = 'TRANSACTION'))
BEGIN
SELECT * FROM MSG
WHERE msg_timestamp >= ( SELECT start_time FROM PROCESS WHERE NAME = 'TRANSACTION' AND STATUS = 'STARTED')
END
ELSE
BEGIN
SELECT * FROM MSG
END
您不需要此do语句，只需使用普通的SQL：
SELECT * FROM MSG
WHERE
NOT EXISTS (SELECT ID FROM PROCESS WHERE ID = (SELECT MAX(ID) FROM PROCESS WHERE NAME = 'TRANSACTION'))
OR msg_timestamp >= (SELECT start_time FROM PROCESS WHERE NAME = 'TRANSACTION' AND STATUS = 'STARTED');
或更详细地说：
SELECT * FROM MSG
WHERE
CASE
WHEN EXISTS (SELECT ID FROM PROCESS WHERE ID = (SELECT MAX(ID) FROM PROCESS WHERE NAME = 'TRANSACTION'))
THEN msg_timestamp >= (SELECT start_time FROM PROCESS WHERE NAME = 'TRANSACTION' AND STATUS = 'STARTED')
ELSE TRUE
END;
或更简单地说：
SELECT * FROM MSG
WHERE
msg_timestamp >= coalesce(
(SELECT start_time FROM PROCESS WHERE NAME = 'TRANSACTION' AND STATUS = 'STARTED'),
'-infinity');
顺便说一句，如果我理解正确
EXISTS (SELECT ID FROM PROCESS WHERE ID = (SELECT MAX(ID) FROM PROCESS WHERE NAME = 'TRANSACTION'))
可以简化为
EXISTS (SELECT 1 FROM PROCESS WHERE NAME = 'TRANSACTION')
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句