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

3 Answers

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.

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

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;