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

stackoverflow.com/…

user330315
– user330315

2021-01-24 14:23:54 +00:00
Commented Jan 24, 2021 at 14:23 — user330315
– user330315, Commented Jan 24, 2021 at 14:23

Abelisto · Accepted Answer · 2021-01-24 17:59:19Z

2

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

edited Jan 24, 2021 at 17:59

answered Jan 24, 2021 at 17:38

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Erwin Brandstetter Over a year ago

Yes to the final simplificaton. The only assumption being that ID is defined NOT NULL. Seems likely, though.

Pavel Stehule · Accepted Answer · 2021-01-24 17:12:42Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related