Postgres 数据库"Query has no destination for result data "错误

Question

我正在尝试将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代替。PL/pgSQL函数inline_code_block第7行在SQL语句FCMDBPOSTGRES = #

我相应的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

Answer 1

您不需要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')

Answer 2

不能工作。Postgres不支持过程或脚本中的自由查询。SQL语句的每个结果都应该保存到变量中(或作为表函数的结果返回)。更多的DO语句没有任何输入输出机制。你可以写表函数:

CREATE OR REPLACE FUNCTION fx()
RETURNS SETOF MSG AS $$
BEGIN
IF EXISTS (SELECT ID FROM PROCESS
WHERE ID = (SELECT MAX(ID) FROM PROCESS
WHERE NAME = 'TRANSACTION'))
THEN
RETURN QUERY SELECT * FROM MSG
WHERE msg_timestamp >= ( SELECT start_time FROM PROCESS
WHERE NAME = 'TRANSACTION' AND STATUS = 'STARTED');
ELSE
RETURN QUERY SELECT * FROM MSG;
END IF;
END
$$ LANGUAGE plpgsql;
SELECT * FROM fx();

注意:如果你有MSSQL过程的经验，那么使用Postgres最好的开始是阅读文档- https://www.postgresql.org/docs/current/plpgsql.html。很多事情都非常非常不同。Postgres中的存储过程类似于Oracle，但与MSSQL相差甚远。