PostgreSQL错误：FROM中的子查询无法引用相同查询级别的其他关系 [英] PostgreSQL ERROR: subquery in FROM cannot refer to other relations of same query level

问题描述

使用CTE作为PostgreSQL函数调用的参数时，我遇到了很多麻烦，似乎没有多少重构子查询的方法；我得到FROM中的子查询不能引用相同查询级别的其他关系或FROM中的函数表达式不能引用相同查询级别的其他关系。简化的SQL：

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

致电：

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"

要点

• 首先创建函数，您的设置中的执行顺序颠倒了


• 在函数相似性中，您必须对列名进行限定，以避免名称与OUT冲突相同名称的参数（ user_id ，距离，解释 ）。


• 您的CTE相似性被破坏了。我将函数调用sameity_gender（..）拉到SELECT列表中。为了避免再次调用，我在下一步中进行了拆分。


• 使用括号访问组合类型的字段。在此处精细手册。


• 返回的相似性函数类型有一个错误：解释hstore 。必须为解释文字。



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.

这篇关于PostgreSQL错误：FROM中的子查询无法引用相同查询级别的其他关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！