CODEWITHSUNDEEP
postgresqlplpgsql
Chammu
Chammu

Reputation: 113

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

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

Upvotes: 0

Views: 6877

Answers (2)

Abelisto
Abelisto

Reputation: 15614

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')

Upvotes: 2

Pavel Stehule
Pavel Stehule

Reputation: 45770

It cannot to work. Postgres doesn't support free queries in procedures or scripts. Every result of SQL statement should be saved to variable (or returned as result of table function). More DO statement has not any input output mechanism. You can write table function:

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();

Note: if you have experience of MSSQL procedures, then the best start for work with Postgres is an reading of documentation - https://www.postgresql.org/docs/current/plpgsql.html . Lot of things are very very different. The stored procedures in Postgres are similar to Oracle, but very far to MSSQL.

Upvotes: 1

Related Questions