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

Answer 1

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.

Answer 2

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