Lateral workaround: Passing an argument to a set-returning function from elsewhere in the query

Question

In PostgreSQL, I'm trying to join a set-returning function that needs arguments from elsewhere in the query.. how can I rewrite this query so it doesn't yield "invalid reference to FROM-clause entry? As I understand it, the query as written would require LATERAL support, which Postgres doesn't have.

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;

I suspect the answer is somewhere in this thread: http://archives.postgresql.org/pgsql-general/2011-08/msg00482.php. But I've played around with various combinations of CTEs, subqueries, OFFSET 0's, etc. and have come up blank; every combination seems to loop forever calling similarity() instead of calling it once and joining it.

Answer 1

You have a couple of issues in your example.

• You try to feed a parameter to a function while at the same time joining to its result, which would affect what is fed to the function in the first place. That kind of vicious circle can never work on principal.

The error message is quite clear on that:

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.

But there is more:

• You cannot feed a whole SET OF values to a function that takes one value.
• You cannot define a function using random() as STABLE.
• Your syntax is inconsistent. Alias for some tables but not for others. Start by straightening that out. Maybe you are confusing yourself.
• You mix the identifiers weighting and weightings. Presumably typos.
• Don't name IN parameters if you are going to refer to them with $n notation anyway. That only creates possible naming conflicts. Or use names that cannot be confused, with a prefix that sets them apart, for instance.

I transformed your demo into something that would work:

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

Maybe this can all be simplified at a lower level.