PostgreSQL: Query has no destination for result data

Question

I am trying to fetch data from remote db by using dblink through function but getting an error query has no destination for result data. I am using plpgsql language to do the same.

Function:

CREATE OR REPLACE FUNCTION fun()
  RETURNS text AS
$$
begin
select dblink_connect(
      'port=5432 dbname=test user=postgres password=****');

WITH a AS (
SELECT *
FROM dblink(
    'SELECT slno,fname,mname,lname
    FROM    remote_tbl'
     ) AS t (slno int, fname text, mname text, lname text)
)
, b AS (
INSERT INTO temptab1
SELECT slno, name
FROM   a
)
, c AS (
INSERT INTO temptab2
SELECT slno, name
FROM   a
)
INSERT INTO temptab3
SELECT slno, name
FROM   a;


select dblink_disconnect();
end;
$$ 
LANGUAGE plpgsql;

Calling Function:

SELECT fun();

ERROR: query has no destination for result data

@bma, Sorry! But I am not getting where do I replace PERFORM? — Sarfaraz Makandar
– Sarfaraz Makandar, Commented May 30, 2014 at 4:03
possible duplicate of Function with SQL query has no destination for result data — Daniel Vérité
– Daniel Vérité, Commented Jun 2, 2014 at 0:42

merqlove · Accepted Answer · 2018-09-12 14:26:58Z

103

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.

edited Sep 12, 2018 at 14:26

merqlove

3,7841 gold badge25 silver badges22 bronze badges

answered May 30, 2014 at 4:02

SL2

1,1741 gold badge8 silver badges9 bronze badges

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

2 Comments

Frank Heikens Over a year ago

Please show your current code, we can't see it... Did you also change "select dblink_connect" ? Every result must have a destination or use PERFORM instead of SELECT.

Sarfaraz Makandar Over a year ago

@Frank Heikens, +1 Yeah I got it. Well! Thank you so much.

mrhotroad · Accepted Answer · 2019-08-22 12:24:13Z

6

Use a plain SQL function instead of PL/PgSQL, or use SELECT INTO and ordinary RETURN.

answered Aug 22, 2019 at 12:24

mrhotroad

3223 silver badges10 bronze badges

Comments

codechefvaibhavkashyap · Accepted Answer · 2020-05-18 08:57:05Z

6

Reason for the error you're getting is because there is no return in between your BEGIN and END for example:

    BEGIN
        update mytable set lastcodeused = to_char(cast(lastcodeused as INTEGER)+1, 'FM999999999999999999') where 
        classid = classIdVar and appid= appIdInt 
        RETURNING concat(pageUniqueCode,lastcodeused) as pageUniqueCode
        into taskcode;
        return taskcode;
    END;

answered May 18, 2020 at 8:57

codechefvaibhavkashyap

1,01520 silver badges33 bronze badges

Comments

Super Kai - Kazuya Ito · Accepted Answer · 2023-12-28 11:14:10Z

I created a PL/pgSQL function as shown below:

CREATE FUNCTION my_func() RETURNS VOID
AS $$
BEGIN
  SELECT 2;
END;
$$ LANGUAGE plpgsql;

Then, calling my_func() got the same error as shown below:

postgres=# SELECT my_func();
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 my_func() line 3 at SQL statement

So, I replaced SELECT statement with PERFORM statement as shown below:

CREATE FUNCTION my_func() RETURNS VOID
AS $$
BEGIN
  -- SELECT 2;
  PERFORM 2;
END;
$$ LANGUAGE plpgsql;

Then, I could call my_func() without error as shown below:

postgres=# SELECT my_func();
 my_func
---------

(1 row)

And, I created a PL/pgSQL function as shown below:

CREATE FUNCTION my_func() RETURNS INTEGER
AS $$
BEGIN
  SELECT 2;
END;
$$ LANGUAGE plpgsql;

Then, calling my_func() got the same error as shown below:

postgres=# SELECT my_func();
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 my_func() line 3 at SQL statement

So, I replaced SELECT statement with RETURN statement as shown below:

CREATE FUNCTION my_func() RETURNS INTEGER
AS $$
BEGIN
  -- SELECT 2;
  RETURN 2; -- Here
END;
$$ LANGUAGE plpgsql;

Or, I put SELECT statement in RETURN statement as shown below:

CREATE FUNCTION my_func() RETURNS INTEGER AS $$
BEGIN
  RETURN (SELECT 2); -- Here
END;
$$ LANGUAGE plpgsql;

Then, I could call my_func() without error as shown below:

postgres=# SELECT my_func();
 my_func
---------
       2
(1 row)

LMigMa49 · Accepted Answer · 2022-12-27 17:05:32Z

If you have this error using a pgplsql procedure or function, and you are sure that the return is defined correctly, there exists a different non-intuitive cause. I needed some time to realize this so I think it is worth sharing. I had a function like this:

CREATE OR REPLACE FUNCTION "db".fn_x(
    id integer)
    RETURNS TABLE(b_val varchar(100), c_val varchar(100)) 
    LANGUAGE 'plpgsql'

AS $BODY$
DECLARE 
var_b_val varchar(100);
var_c_val varchar(100);

BEGIN
    select var_b, var_c
        -- Missing INTO clause was the cause of the error.
        var_b_val, var_c_val
        from "db".table_y where y_id = id;

    return query(select var_b_val, var_c_val);
END;
$BODY$;

Just adding that missing INTO clause made the function work correctly.

In conclusion, this error can also trigger on silent syntax errors.

Collectives™ on Stack Overflow

PostgreSQL: Query has no destination for result data

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related