postgresql - Postgres database "Query has no destination for result data " error

Question

I am trying to convert MSSQL queries to POSTGRES queries. I am not able to execute the below query in 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 $$;

ERROR:

query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function inline_code_block line 7 at SQL statement FCMDBPOSTGRES=#

My corresponding MSSQL query is as below which works fine

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

question from:https://stackoverflow.com/questions/65871523/postgres-database-query-has-no-destination-for-result-data-error

Answer 1

You do not need the do statement for this, just plain 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');

Or to be more verbose:

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;

Or to be more simple:

SELECT * FROM MSG
WHERE 
    msg_timestamp >= coalesce(
        (SELECT start_time FROM PROCESS WHERE NAME = 'TRANSACTION' AND STATUS = 'STARTED'),
        '-infinity');

BTW If I understand correctly

EXISTS (SELECT ID FROM PROCESS WHERE ID = (SELECT MAX(ID) FROM PROCESS WHERE NAME = 'TRANSACTION'))

could be simplified to

EXISTS (SELECT 1 FROM PROCESS WHERE NAME = 'TRANSACTION')