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 P开发者_运维知识库ostgreSQL function call, and no amount of refactoring into subqueries seems to help; I get either subquery in FROM cannot refer to other relations of same query level or function expression in FROM cannot refer to other relations of same query level. The simplified SQL:

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

create or replace function similarity(my_user_id int)
returns table(user_id int, distance float, explanation hstore) as $$

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

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

  similarities as (
    select factors.user_id, sim.distance, sim.explanation
    from factors, my_factors, similarity_gender(my_factors.gender, factors.gender) as sim)

  select user_id, distance, explanation from similarities;
$$ language sql stable strict;


create or replace function similarity_gender(my_gender text, other_gender text) returns similarity as $$
  declare
    distance  float;
    sim       similarity;
  begin
    if my_gender is null or other_gender is null then
      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;

---

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
create table x.users(id int);
create table x.demographics (user_id int, gender text);

INSERT INTO x.users VALUES (1),(2),(3),(4),(5);
INSERT INTO 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
);

create or 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 is null or other_gender is null then
      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;


create or replace function x.similarity(my_user_id int)
returns table(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;
$$ language sql 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.