PostgreSQL：查询没有结果数据的目的地 [英] PostgreSQL: Query has no destination for result data

问题描述

我正在尝试通过函数使用dblink从远程db获取数据，但出现错误查询没有结果数据的目的地。我正在使用plpgsql语言执行相同的操作。

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.

功能：

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;

呼叫功能：

select fun();

错误：查询没有结果数据的目的地

Error: query has no destination for result data

推荐答案

存储过程将不仅仅返回最后一个SELECT的结果。您实际上需要返回值：

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;

出现错误是因为Postgres期望函数返回文本类型的东西，但是您的函数不返回任何内容。

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

这篇关于PostgreSQL：查询没有结果数据的目的地的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！