错误：形式上的函数表达式无法引用相同查询级别的其他关系：如何解决LATERAL [英] ERROR: function expression in form cannot refer to other relations of same query level : How to work around LATERAL

问题描述

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE primes
        ( pos SERIAL NOT NULL PRIMARY KEY
        , val INTEGER NOT NULL
        , CONSTRAINT primes_alt UNIQUE (val)
        );

CREATE FUNCTION is_prime(_val INTEGER)
RETURNS BOOLEAN
AS $func$
DECLARE ret BOOLEAN ;
BEGIN

SELECT False INTO ret
WHERE EXISTS (SELECT *
        FROM primes ex
        WHERE ex.val = $1
        OR ( (ex.val * ex.val) <= $1 AND ($1 % ex.val) = 0 )
        );
RETURN COALESCE(ret, True);
END;
$func$ LANGUAGE plpgsql STABLE;

CREATE VIEW vw_prime_step AS (
        -- Note when the table is empty we return {2,3,1} as a bootstrap
        SELECT
           COALESCE(MAX(val) +2,2) AS start
         , COALESCE((MAX(val) * MAX(val))-1, 3) AS stop
         , COALESCE(min(val), 1) AS step
        FROM primes
        );
SELECT * FROM vw_prime_step;

        -- The same as a function.
        -- Works, but is not usable in a query that alters the primes table.
        -- ; even not with the TEMP TABLE construct
CREATE FUNCTION fnc_prime_step ( OUT start INTEGER, OUT stop INTEGER, OUT step INTEGER)
RETURNS RECORD
AS $func$
BEGIN
/***
CREATE TEMP TABLE tmp_limits
        ON COMMIT DROP
        AS SELECT ps.start,ps.stop,ps.step FROM vw_prime_step ps
        ;
-- RETURN QUERY 
SELECT tl.start,tl.stop,tl.step INTO $1,$2,$3
FROM tmp_limits tl
LIMIT 1
        ; 
***/
SELECT tl.start,tl.stop,tl.step INTO $1,$2,$3
FROM vw_prime_step tl
LIMIT 1;
END;
$func$
-- Try lying ...
-- IMMUTABLE LANGUAGE plpgsql;
-- Try lying ...
Stable LANGUAGE plpgsql;

        -- This works
SELECT * FROM fnc_prime_step();
INSERT INTO primes (val)
SELECT gs FROM fnc_prime_step() sss
 , generate_series( 2, 3, 1 ) gs
WHERE is_prime(gs) = True
        ;
        -- This works
SELECT * FROM fnc_prime_step();
INSERT INTO primes (val)
SELECT gs FROM fnc_prime_step() sss
 , generate_series( 5, 24, 2 ) gs
WHERE is_prime(gs) = True
        ;

        -- This does not work
        -- ERROR:  function expression in FROM cannot refer to other relations of same query level:1
SELECT * FROM fnc_prime_step();
INSERT INTO primes (val)
SELECT gs FROM fnc_prime_step() sss
 , generate_series( sss.start, sss.stop, sss.step ) gs
WHERE is_prime(gs) = True
        ;

SELECT * FROM primes;
SELECT * FROM fnc_prime_step();

当然，这个问题纯粹是假设的，我不够愚蠢，无法尝试计算表DBMS中的质数。但是问题仍然存在：是否有一种干净的方法可以解决 LATERAL 的缺失？

Of course, this question is purely hypothetic, I am not stupid enough to attempt to calculate a table of prime numbers in an DBMS. But the question remains: is there a clean way to hack around the absence of LATERAL?

如您所见，我尝试了一个视图（不起作用），围绕该视图的功能（也不起任何作用），该函数中的临时表（njet）以及旋转功能的属性。

As you can see, I tried with a view (does not work), function around this view (does not work either), a temp table in this function (njet), and twiddling the function's attributes.

下一步可能是一些触发器黑客（但我真的非常讨厌触发器，基本上是因为DBMS模式的严格性看不见触发器）

Next step will probably be some trigger-hack (but I really,really hate triggers, basically because they are invisible to the strictness of the DBMS schema)

推荐答案

您可以在目标列表中使用SRF函数，但是应该有一些奇怪的极端情况。横向是最好的。

you can use SRF function in target list, but there should be some strange corner cases. LATERAL is best.

postgres=# select i, generate_series(1,i) X from generate_series(1,3) g(i);
 i | x 
---+---
 1 | 1
 2 | 1
 2 | 2
 3 | 1
 3 | 2
 3 | 3
(6 rows)

这篇关于错误：形式上的函数表达式无法引用相同查询级别的其他关系：如何解决LATERAL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！