CODEWITHSUNDEEP
sqldatabasepostgresqlplpgsql
Sarfaraz Makandar
Sarfaraz Makandar

Reputation: 6733

PostgreSQL: Query has no destination for result data

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

Upvotes: 76

Views: 214009

Answers (5)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

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)

Upvotes: 4

LMigMa49
LMigMa49

Reputation: 111

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.

Upvotes: 3

codechefvaibhavkashyap
codechefvaibhavkashyap

Reputation: 1015

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;

Upvotes: 6

mrhotroad
mrhotroad

Reputation: 313

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

Upvotes: 5

SL2
SL2

Reputation: 1164

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.

Upvotes: 102

Related Questions