SQL查询的功能没有结果数据的目的地 [英] Function with SQL query has no destination for result data
本文介绍了SQL查询的功能没有结果数据的目的地的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试创建一个返回SELECTed结果集的函数.
当我像这样select * from tst_dates_func()
调用我的postgres函数时,出现如下所示的错误:
I am trying to create a function that returns a SELECTed resultset.
When I call my postgres function like this select * from tst_dates_func()
I get an error as shown below:
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 "tst_dates_func" line 3 at SQL statement
********** Error **********
ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Context: PL/pgSQL function "tst_dates_func" line 3 at SQL statement
这是我创建的函数:
CREATE OR REPLACE FUNCTION tst_dates_func()
RETURNS TABLE( date_value date, date_id int, date_desc varchar) as
$BODY$
BEGIN
select a.date_value, a.date_id, a.date_desc from dates_tbl a;
END;
$BODY$
LANGUAGE plpgsql;
我不确定为什么会出现上述错误.我想运行select * from tst_dates_func();
并取回数据.或根据需要进一步加入结果集.这是什么问题?
I am not sure why I am getting the above error. I would like to run select * from tst_dates_func();
and get data back. Or further join the result set if needed. What is the problem here?
推荐答案
以纯SQL格式
CREATE OR REPLACE FUNCTION tst_dates_func()
RETURNS TABLE( date_value date, date_id int, date_desc varchar) as
$BODY$
select a.date_value, a.date_id, a.date_desc from dates_tbl a;
$BODY$
LANGUAGE sql;
如果您确实需要plpgsql,请使用return query
If you really need plpgsql use return query
CREATE OR REPLACE FUNCTION tst_dates_func()
RETURNS TABLE( date_value date, date_id int, date_desc varchar) as
$BODY$
BEGIN
perform SELECT dblink_connect('remote_db');
return query
select a.date_value, a.date_id, a.date_desc from dates_tbl a;
END;
$BODY$
LANGUAGE plpgsql;
这篇关于SQL查询的功能没有结果数据的目的地的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文