Postgresql Error: Subquery In From Cannot Refer To Other Relations Of Same Query Level

I'm having an inordinate amount of trouble using CTEs as arguments to a PostgreSQL function call, and no amount of refactoring into subqueries seems to help; I get either subquery

Solution 1:

For debugging, I created the test scenario: _{You should have included that in your setup in the question.}

-- drop schema x CASCADE;create schema x
createtable x.users(id int);
createtable x.demographics (user_id int, gender text);

INSERTINTO x.users VALUES (1),(2),(3),(4),(5);
INSERTINTO x.demographics VALUES (1, 'm'),(2, 'f'),(3, 'm'),(4, 'f'),(5, 'm');

This works now, after some fixes:

create type x.similarity as (
  distance    float,
  explanation text
);

createor replace function x.similarity_gender(my_gender text, other_gender text)
returns x.similarity as $$
  declare
    distance  float;
    sim       x.similarity;
  begin
    if my_gender isnullor other_gender isnullthen
      distance =0.9;
    elsif (my_gender = other_gender) then
      distance =0.0;
    else
      distance =1.0;
    end if;

    sim.distance     = distance;
    sim.explanation  = hstore('gender', cast(sim.distance as text));
    return sim;
  end;
$$ language plpgsql immutable;


createor replace function x.similarity(my_user_id int)
returnstable(user_id int, distance float, explanation text) as $$

  with factors as (
    select u.id as user_id, d.gender
    from x.users u
    join x.demographics d on u.id = d.user_id),

  my_factors as (
    select f.user_id, f.gender
    from factors  f
    where f.user_id = $1),

  similarities as (
    select f.user_id, x.similarity_gender(m.gender, f.gender) AS sim
    from factors f, my_factors m)

  select s.user_id, (s.sim).distance, (s.sim).explanation
    from similarities s;
$$ languagesql stable strict;

Call:

test=# SELECT*FROM x.similarity(2);
 user_id | distance |  explanation
---------+----------+---------------1|1| "gender"=>"1"
       2|0| "gender"=>"0"
       3|1| "gender"=>"1"
       4|0| "gender"=>"0"
       5|1| "gender"=>"1"

Major points

• Create the function first, you have execution sequence reversed in your setup
• In function similarity you must qualify columns names to avoid name conflicts with OUT parameters of the same name (user_id, distance, explanation).
• Your CTE similarities was mangled. I pulled the function call similarity_gender(..) up into the SELECT list. In order not to call twice, I split it up in the next step.
• Use parenthesis to access the fields of composite types. Consult the fine manual here.
• Return type of function similarity() had a bug: explanation hstore. Must be explanation text.