所以我写了一个像这样的斐波那契数列函数:
CREATE OR REPLACE FUNCTION fibonacci (lastN INTEGER)
RETURNS int AS $$
BEGIN
WITH RECURSIVE t(a, b) AS (
VALUES(0,1)
UNION ALL
SELECT GREATEST(a, b), a + b AS a from t
WHERE b < $1
)
SELECT a FROM t;
END;
$$ LANGUAGE plpgsql;
但当我打电话时:
SELECT * FROM fibonacci(20);
控制台显示:
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 fibonacci(integer) line 5 at SQL statement
我认为 Return 语句应该返回查询结果,但事实并非如此。对于编写这样的 SQL 函数,我完全是个新手。
你很接近。基本上你的SELECT
无处可去,你的函数头说它应该返回一个INT
价值。既然是纯粹的SQL
查询使用CTE
,没有必要使用PLPGSQL
,所以我也将语言类型更改为SQL
CREATE OR REPLACE FUNCTION fibonacci (lastN INTEGER)
RETURNS SETOF INTEGER LANGUAGE SQL AS $$
WITH RECURSIVE t(a, b) AS (
VALUES(0,1)
UNION ALL
SELECT GREATEST(a, b), a + b AS a from t
WHERE b < $1
)
SELECT a FROM t;
$$;
SELECT fibonacci(20);
EDIT:根据要求,使用该语言的相同功能PLPGSQL
CREATE OR REPLACE FUNCTION fibonacci (lastN INTEGER)
RETURNS SETOF INT LANGUAGE PLPGSQL AS $$
BEGIN
RETURN QUERY WITH RECURSIVE t(a, b) AS (
VALUES(0,1)
UNION ALL
SELECT GREATEST(a, b), a + b AS a from t
WHERE b < $1
)
SELECT a FROM t;
END $$;
SELECT fibonacci(20);
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)