postgresql - Postgresql 查询的奇怪行为

Question

我们在 Postgres 中创建了一个 View ，但我得到了奇怪的结果。

View 名称:event_puchase_product_overview

当我尝试使用 * 获取记录时，我得到了正确的结果。但是当我尝试获取特定字段时，我得到了错误的值。

我希望这里附上的屏幕能很好地说明问题。

select * 
from event_purchase_product_overview 
where id = 15065;

select id, departure_id 
from event_puchase_product_overview 
where id = 15065;

查看定义:

CREATE OR REPLACE VIEW public.event_puchase_product_overview AS 
 SELECT row_number() OVER () AS id,
    e.id AS departure_id,
    e.type AS event_type,
    e.name,
    p.id AS product_id,
    pc.name AS product_type,
    product_date.attribute AS option,
    p.upcomming_date AS supply_date,
    pr.date_end AS bid_deadline,
        CASE
            WHEN (pt.categ_id IN ( SELECT unnest(tt.category_ids) AS unnest
               FROM ( SELECT string_to_array(btrim(ir_config_parameter.value, '[]'::text), ', '::text)::integer[] AS category_ids
                       FROM ir_config_parameter
                      WHERE ir_config_parameter.key::text = 'trip_product_flight.product_category_hotel'::text) tt)) THEN e.maximum_rooms
            WHEN (pt.categ_id IN ( SELECT unnest(tt.category_ids) AS unnest
               FROM ( SELECT string_to_array(btrim(ir_config_parameter.value, '[]'::text), ', '::text)::integer[] AS category_ids
                       FROM ir_config_parameter
                      WHERE ir_config_parameter.key::text = 'trip_product_flight.product_category_flight'::text) tt)) THEN e.maximum_seats
            WHEN (pt.categ_id IN ( SELECT unnest(tt.category_ids) AS unnest
               FROM ( SELECT string_to_array(btrim(ir_config_parameter.value, '[]'::text), ', '::text)::integer[] AS category_ids
                       FROM ir_config_parameter
                      WHERE ir_config_parameter.key::text = 'trip_product_flight.product_category_bike'::text) tt)) THEN e.maximum_bikes
            ELSE e.maximum_seats
        END AS departure_qty,
        CASE
            WHEN now()::date > pr.date_end AND po.state::text = 'draft'::text THEN true
            ELSE false
        END AS is_deadline,
    pl.product_qty::integer AS purchased_qty,
    pl.comments,
    pl.price_unit AS unit_price,
    rp.id AS supplier,
    po.id AS po_ref,
    po.state AS po_state,
    po.date_order AS po_date,
    po.user_id AS operator,
    pl.po_state_line AS line_status
   FROM event_event e
     LEFT JOIN product_product p ON p.related_departure = e.id
     LEFT JOIN product_template pt ON pt.id = p.product_tmpl_id
     LEFT JOIN product_category pc ON pc.id = pt.categ_id
     LEFT JOIN purchase_order_line pl ON pl.product_id = p.id
     LEFT JOIN purchase_order po ON po.id = pl.order_id
     LEFT JOIN purchase_order_purchase_requisition_rel prr ON prr.purchase_order_id = po.id
     LEFT JOIN purchase_requisition pr ON pr.id = prr.purchase_requisition_id
     LEFT JOIN res_partner rp ON rp.id = po.partner_id
     LEFT JOIN ( SELECT p_1.id AS product_id,
            pav.name AS attribute
           FROM product_product p_1
             LEFT JOIN product_attribute_value_product_product_rel pa ON pa.prod_id = p_1.id
             LEFT JOIN product_attribute_value pav ON pav.id = pa.att_id
             LEFT JOIN product_attribute pat ON pat.id = pav.attribute_id
          WHERE pat.name::text <> ALL (ARRAY['Date'::character varying, 'Departure'::character varying]::text[])) product_date ON product_date.product_id = p.id
  WHERE (p.id IN ( SELECT DISTINCT mrp_bom_line.product_id
           FROM mrp_bom_line)) AND p.active
  ORDER BY e.id, pt.categ_id, p.id;

Answer 1

如果我添加新的 event_event 或新的 product_product，我将在我的 View 中获得 row_number 的新定义，然后我的 View 的列 ID 不稳定。

至少你不能使用 row_number 作为 View 的 Id，

如果你坚持使用row_number，你可以使用Order By "creation DATE"这样所有的新记录都将作为 View 中的最后一行，这不会改变ID(row_number)和其他列之间的对应关系。

希望对您有所帮助!