Блог

В PostgreSQL я пытаюсь присоединиться к функции, возвращающей набор, которой требуются аргументы из другого места запроса.. как я могу переписать этот запрос, чтобы он не выдавал «недопустимую ссылку на запись в предложении FROM?»? Насколько я понимаю, для запроса в том виде, в каком он написан, потребуется БОКОВАЯ поддержка, которой у Postgres нет.

 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, подзапросов, СМЕЩЕНИЯ 0 и т.д. И получил пустой результат; кажется, что каждая комбинация бесконечно повторяет вызов similarity() вместо того, чтобы вызывать ее один раз и присоединяться к ней.

В вашем примере есть пара проблем.

• Вы пытаетесь передать параметр функции, одновременно присоединяясь к ее результату, что в первую очередь повлияло бы на то, что передается функции. Такой порочный круг никогда не сможет работать с principal.

Сообщение об ошибке довольно ясно указывает на это:

 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. Это только создает возможные конфликты именования. Или используйте имена, которые нельзя перепутать, например, с префиксом, который отличает их друг от друга.

Я преобразовал вашу демонстрацию во что-то, что будет работать:

 -- 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);
  

Возможно, все это можно упростить на более низком уровне.