StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPostgreSQL ERROR: subquery in FROM cannot refer to other relations of same query level
text
Body
copied!<p>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 <code>subquery in FROM cannot refer to other relations of same query level</code> or <code>function expression in FROM cannot refer to other relations of same query level</code>. The simplified SQL:</p> <pre><code>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; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload