横向解决方法：从查询中的其他位置将参数传递给设置返回函数

Question

在 PostgreSQL 中，我试图加入一个需要查询中其他地方的参数的集合返回函数。如何重写此查询，使其不会产生“对 FROM 子句条目的无效引用？据我了解，所写的查询需要横向支持，

drop table if exists questions;
create table questions (
  id int not null primary key,
  user_id int not null
);
insert into questions
  select generate_series(1,1100), (random()*20000)::int;

drop table if exists users;
create table users (
  id int not null primary key
);
insert into users select generate_series(1, 20000);

drop function if exists question_weightings();
create function question_weightings()
returns table (question_id int, weighting int) as $$
  select questions.id, (random()*10)::int
  from questions;
$$ language sql stable;

drop function if exists similarity(int);
create function similarity(weighting int)
returns table (user_id int, score int) as $$
  select users.id,  (random() * $1)::int
  from users;
$$ language sql stable;

select questions.id, qw.weightings
from questions
join question_weightings() as qw
on qw.question_id = questions.id
join similarity(qw.weighting) as sim
on sim.user_id = questions.user_id;

我怀疑答案就在这个线程中的某个地方： http://archives.postgresql.org/pgsql-general/2011-08 /msg00482.php。 但我尝试过 CTE、子查询、OFFSET 0 等的各种组合，但结果都是空白；每个组合似乎都会永远循环调用相似度（），而不是调用一次并加入它。

Answer 1

您的示例中有几个问题。

• 您尝试向函数提供参数，同时加入其结果，这将首先影响向函数提供的内容。这种恶性循环在原则上是永远行不通的。

错误消息对此非常清楚：

ERROR:  invalid reference to FROM-clause entry for table "qw"
LINE 5: join similarity(qw.weighting) as sim on sim.user_id = questi...
                        ^
HINT:  There is an entry for table "qw", but it cannot be referenced from this part of the query.

但还有更多：

• 您无法将整个值集提供给采用一个值的函数。
• 您不能将使用 random() 的函数定义为STABLE。
• 你的语法不一致。某些表的别名，但其他表的别名。首先解决这个问题。也许你自己也很困惑。
• 您可以混合使用标识符weighting 和weightings。估计是错别字。
• 如果您打算使用 $n 表示法来引用 IN 参数，请勿命名它们。这只会造成可能的命名冲突。或者使用不会混淆的名称，例如使用前缀将它们分开。

我将您的演示转变为可行的东西：

-- DROP SCHMEMA x CASCADE;
CREATE SCHEMA x

CREATE TABLE x.questions (id int PRIMARY KEY, user_id int NOT NULL);
INSERT INTO x.questions SELECT generate_series(1,11), (random()*20000)::int;

CREATE TABLE x.users (id int PRIMARY KEY);
INSERT INTO x.users SELECT generate_series(1, 200);

CREATE FUNCTION x.question_weighting()
  RETURNS TABLE (question_id int, weighting int) AS 
$BODY$
SELECT q.id, (random()*10)::int
FROM   x.questions q;
$BODY$
  LANGUAGE sql;

CREATE FUNCTION x.similarity(int)
  RETURNS TABLE (user_id int, score int) AS
$BODY$
  SELECT u.id, (random() * $1)::int
  FROM   x.users u;
$BODY$
  LANGUAGE sql;

WITH qqw AS (
    SELECT q.id, q.user_id, qw.weighting
    FROM   x.questions q
    JOIN   x.question_weighting() qw ON qw.question_id = q.id
    -- WHERE  ??
    )
SELECT id, weighting
FROM   qqw
JOIN   (
    SELECT *
    FROM   x.similarity((
        SELECT weighting
        FROM   qqw      
        -- WHERE ??
        LIMIT  1
        ))
    ) sim USING (user_id);

也许这一切都可以在较低级别上简化。