Postgres:自引用N:N使用函数获取数据

Question

我想返回component_id的ingredient_title，但是一直获取ingredient_id的标题，似乎无法理解其中的逻辑。

返回数组，因为可以有多个成分具有相同的限制数量。

ingredients_components的连接表ingredient_id和component_id都是指向ingredients.ingredient_id的外键，并且都是此连接表的主键

重新创建示例

CREATE TABLE ingredients (
  ingredient_id SERIAL PRIMARY KEY,
  ingredient_title VARCHAR(255),
  ingredient_quantity NUMERIC(8,2) DEFAULT 0
);

INSERT INTO ingredients(ingredient_title, ingredient_quantity)
VALUES
('Frosting', 5.00),
('Egg', 13.00),
('Butter', 8.00);

CREATE TABLE ingredients_components (
  ingredient_id INT REFERENCES ingredients (ingredient_id),
  component_id INT REFERENCES ingredients (ingredient_id),
  required_component_quantity NUMERIC(8,2) DEFAULT 0,
  CONSTRAINT ingredient_component PRIMARY KEY (ingredient_id, component_id)
);

INSERT INTO ingredients_components(ingredient_id, component_id, required_component_quantity)
VALUES
(1, 2, 2.00),
(1, 3, 4.00);

我的函数

CREATE OR REPLACE FUNCTION fn_part_limiting_components_title(arg_ingredient_id INT) RETURNS VARCHAR[] AS
$$
    SELECT array_agg(ingredient_title)
    FROM
    (
        SELECT
            ingredient_title,
            FLOOR(ingredient_quantity/required_component_quantity) AS limiting_component_quantity
        FROM ingredients
            INNER JOIN ingredients_components
            ON ingredients.ingredient_id = ingredients_components.ingredient_id
        WHERE ingredients_components.ingredient_id = arg_ingredient_id
            AND FLOOR(ingredient_quantity/required_component_quantity) =
            (
                SELECT
                FLOOR(ingredient_quantity/required_component_quantity) AS limiting_component_quantity
                FROM ingredients
                    INNER JOIN ingredients_components
                    ON ingredients.ingredient_id = ingredients.ingredient_id
                WHERE ingredients.ingredient_id = arg_ingredient_id
                ORDER BY limiting_component_quantity ASC
                LIMIT 1
            )
        ORDER BY limiting_component_quantity ASC
    ) AS limiting_component
$$
LANGUAGE SQL;

--RUN Function
SELECT fn_part_limiting_components_title(1);

Answer 1

您的查询可以通过使用窗口函数来计算最小限制数量来简化: select ingredient_id，ingredient_title，limiting_component_quantity from ( select i.*，ingredient_id as limiting_component_quantity，dense_rank() over (order by FLOOR(ingredient_quantity/required_component_quantity)) AS limiting_component_quantity，dense_rank()over(order by rnk as rnk from ic.component_id join ingredients_components ic on i.ingredient_id =ic.component_id where ic.ingredient_id =1)t where rnk= 1；

我会将其放入一个集返回函数中：

create function  fn_part_limiting_components_title(arg_ingredient_id integer)
  returns table (ingredient_id integer, 
                 ingredient_title text, 
                 limiting_component_quantity numeric)
as
$$
 select ingredient_id, ingredient_title, limiting_component_quantity
 from (
    select i.*,
           FLOOR(ingredient_quantity/required_component_quantity) AS limiting_component_quantity,
           dense_rank() over (order by FLOOR(ingredient_quantity/required_component_quantity)) as rnk
    from ingredients i
      join ingredients_components ic on i.ingredient_id = ic.component_id
    where ic.ingredient_id = arg_ingredient_id
 ) t
 where rnk = 1;
$$
language sql;

然后像这样使用它：

select *
from fn_part_limiting_components_title(1);