Postgresql函数在选择时不返回表 [英] Postgresql function not returning a table on select

问题描述

我有以下postgresql函数，在其中我试图返回2个名为Campusid和Campusname的参数。

I have the following postgresql function in which i am trying to return 2 parameters named campusid and campusname.

CREATE OR REPLACE FUNCTION getall(IN a character varying, IN b character varying)
  RETURNS TABLE(id character varying, name character varying) AS
$BODY$
BEGIN
if $1 = 'PK' then
     SELECT * from table1;

end if;
END
$BODY$
LANGUAGE plpgsql;

但是我遇到以下错误：

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 "getallcampuses" line 27 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 "getallcampuses" line 27 at SQL statement

的PL / pgSQL函数 getallcampuses第27行

我该怎么办需要更改函数以使其返回值表吗？我还检查了性能查询，但我需要返回结果。

What do i need to change in the function to make it return me a table of values? I have also checked the perform query but i need to return a result.

推荐答案

选择的目的地，并且该函数必须返回一个值。只是SELECT语句都不做。通常，此类语句的唯一用途是测试权限或执行触发器，而不会使用结果。您将需要使用RETURN语句之一来从函数中获取值。

You must have a destination for the selects, and the function must return a value. Just a SELECT statement does neither. The only use of such a statement, generally, is to test permissions, or make a trigger run, for which the results are not used. You will need to use one of the family of RETURN statements, to get values from the function.

RETURN QUERY( SELECT * from "SIS_campus" );

这会将查询的结果添加到函数的返回结果中，并应执行您要执行的操作之后，因为您只能返回0或1个结果。您可能还需要在函数的末尾添加一个简单的RETURN（尽管文档，我本人并不太讨厌何时需要或不需要）。

That will add the results of that query to the function's returning results, and should do what you're after, since you only can return 0 or 1 results. You may need to add a simple RETURN at the very end of the function, as well (despite the docs, I've not quite grokked when that is or isn't needed, myself).

这篇关于Postgresql函数在选择时不返回表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！