PostgreSQL：查询没有结果数据的目的地
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23946735/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL: Query has no destination for result data
提问by Meem
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.
我试图通过函数使用 dblink 从远程数据库获取数据，但收到错误“查询没有结果数据的目的地”。我正在使用 plpgsql 语言来做同样的事情。
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
错误：查询没有结果数据的目的地
回答by SL2
The stored procedure won't just return the result of the last SELECT. You need to actually return the value:
存储过程不会只返回最后一个 SELECT 的结果。您需要实际返回值：
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.
您收到错误是因为 Postgres 期望函数返回文本类型的内容，但您的函数不返回任何内容。
回答by codechefvaibhavkashyap
Reason for the error you're getting is because there is no returnin between your BEGINand ENDfor example:
您收到错误的原因是因为您的BEGIN和END之间没有返回，例如：
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;
回答by merqlove
Answer from SL2 was right, but you can write it in the short way:
SL2 的回答是正确的，但你可以用简短的方式写出来：
CREATE OR REPLACE FUNCTION fun() RETURNS text AS $$
BEGIN
--- ....
RETURN(SELECT dblink_disconnect());
END
$$ LANGUAGE plpgsql;
回答by mrhotroad
Use a plain SQL function instead of PL/PgSQL, or use SELECT INTOand ordinary RETURN.
使用普通 SQL 函数而不是 PL/PgSQL，或者使用SELECT INTO普通的RETURN.
