Error: Query Has No Destination for Result Data

PostgreSQL: Query has no destination for result data

The stored procedure won't just return the result of the last SELECT. You need to actually return the value:

CREATE OR REPLACE FUNCTION fun() RETURNS text AS $$
BEGIN
    --- ....
    RETURN(SELECT dblink_disconnect());
END
$$ LANGUAGE plpgsql;

You're getting the error because Postgres expects the function to return something of type text, but your function doesn't return anything.

Postgres database Query has no destination for result data error

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

POSTGRESQL- Query has no destination for result data

If you are executing a select statement in a PL/pgSQL function, then you should place the result of the query in some variable(s) (= the destination). Then you work with the variable(s) in the function. You should also have a RETURN statement.

create or replace function country(text) returns text as $$
declare                   -- declare some variables
  id integer;
  fname text;
  lname text;
begin
  select customer_id, customer.first_name, customer.last_name 
    into id, fname, lname -- store query results in variables
  from customer 
  inner join address on customer.address_id = address.address_id 
  inner join city on address.city_id = city.city_id 
  inner join country on city.country_id = country.country_id 
  where country = $1;     -- don't quote parameter references

  -- do something with the variables and return a value from the function
  return format('%s: %s %s', id, upper(lname), fname);
end;
$$ language plpgsql;

Do note that the above only works if the query returns a single row. If the query returns multiple rows you can use a loop in the function. Even simpler, you can just return the results from the query like so:

create or replace function country(text)
returns table (id integer, first_name varchar, last_name varchar) as $$
begin
  return query
    select customer_id, customer.first_name, customer.last_name 
    from customer 
    inner join address on customer.address_id = address.address_id 
    inner join city on address.city_id = city.city_id 
    inner join country on city.country_id = country.country_id 
    where country = $1;
end;
$$ language plpgsql;

But like Evan Carroll said, unless you need a PL/pgSQL function to modify the data before returning it, you are better off with a simple view.

Function with SQL query has no destination for result data

Do it as plain SQL

CREATE OR REPLACE FUNCTION tst_dates_func() 
    RETURNS TABLE( date_value date, date_id int, date_desc varchar) as
$BODY$   
    select a.date_value, a.date_id, a.date_desc from dates_tbl a;

$BODY$
      LANGUAGE sql;

If you really need plpgsql use return query

CREATE OR REPLACE FUNCTION tst_dates_func() 
    RETURNS TABLE( date_value date, date_id int, date_desc varchar) as
$BODY$   
BEGIN
    perform SELECT dblink_connect('remote_db');
    return query
    select a.date_value, a.date_id, a.date_desc from dates_tbl a;

END;
$BODY$
      LANGUAGE plpgsql;

Error Message : Query has no destination for result data

I assume you're attempting to return the query result with your function. You actually do not need plpgsql language for this, but in case you need it for somthing else, use this syntax:

CREATE OR REPLACE FUNCTION roomType (int_inst_id int)
RETURNS TABLE (res_nclient_room_type_id INT,res_sclient_rt_desc TEXT,res_sclient_rt_name TEXT, res_sclient_rt_code TEXT)
AS $$
BEGIN
    RETURN QUERY 
    SELECT 
        nclient_room_type_id,sclient_rt_desc,sclient_rt_name,sclient_rt_code
    FROM 
        clientroomtype
    WHERE 
        clientroomtype.ninst_id=int_inst_id
    ORDER BY 
        clientroomtype.sclient_rt_code;
END;
$$ LANGUAGE plpgsql;

How to use it?

SELECT * FROM roomType(1);

Since I do not have your data, I cannot test it. But it follows this principle:

CREATE OR REPLACE FUNCTION f ()
RETURNS TABLE (b boolean, x int)
AS $$
BEGIN
    RETURN QUERY SELECT TRUE, 1;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM f();

 b | x 
---+---
 t | 1
(1 Zeile)

---

Related Topics