如何为单产品配置多价格并实现查询仅返回用户对应价格？

问题背景 #

现有products表存储产品数据，需实现产品多价格逻辑：每个产品包含默认价格，同时支持手动设置不同VIP等级（如vip1、vip2）的自定义专属价格。用户查询产品时，需通过视图根据其VIP等级返回对应价格——优先返回匹配等级的自定义价格，无匹配则返回默认价格。已实现根据用户points值判断VIP等级的逻辑，目前纠结价格存储方式：是在products表新增列，还是创建单独的价格表？

已实现的方案 #

1. 数据库表结构 #

vip表（VIP等级配置）

create table
  public.vip (
    id bigint generated by default as identity,
    level smallint not null,
    points integer not null,
    constraint vip_pkey primary key (id),
    constraint vip_level_key unique (level)
  ) tablespace pg_default;

profiles表（用户积分与身份关联）

create table
  public.profiles (
    id uuid not null,
    metadata jsonb null,
    authorization jsonb null,
    points integer not null default 0,
    constraint profiles_pkey primary key (id),
    constraint profiles_id_key unique (id),
    constraint profiles_id_fkey foreign key (id) references auth.users (id) on delete cascade
  ) tablespace pg_default;

products表（基础产品数据）

create table
  public.products (
    id bigint generated by default as identity,
    is_category boolean not null default false,
    is_available boolean not null default false,
    is_hidden boolean not null default true,
    is_main boolean null default false,
    created_at timestamp with time zone not null default now(),
    parent_id bigint null,
    min_value integer null default 0,
    max_value integer null default 0,
    metadata jsonb null,
    price numeric null default '0'::numeric,
    constraint products_pkey primary key (id),
    constraint products_parent_id_fkey foreign key (parent_id) references products (id) on delete cascade
  ) tablespace pg_default;

product_vip_price表（产品VIP专属价格表）

选择单独建表存储VIP价格，通过product_id关联产品：

create table
  public.product_vip_price (
    id bigint generated by default as identity,
    product_id bigint not null,
    vip_level smallint null,
    price numeric null,
    constraint product_vip_price_pkey primary key (id),
    constraint product_vip_price_product_id_fkey foreign key (product_id) references products (id) on delete cascade
  ) tablespace pg_default;

2. 初始VIP等级判断逻辑 #

通过函数user_highest_vip_is()获取当前用户的最高VIP等级：

create
or replace function user_highest_vip_is () returns text as $$ BEGIN RETURN (SELECT max(vip.level) FROM profiles JOIN vip ON profiles.points >= vip.points WHERE profiles.id = auth.uid()); END; $$ language plpgsql security definer;

3. 初始产品视图（products_view） #

通过左关联product_vip_price表，使用coalesce函数优先返回匹配VIP等级的价格，无匹配则返回产品默认价格：

create view
  public.products_view as
select
  products.id,
  products.is_main,
  products.is_available,
  products.is_category,
  products.parent_id,
  products.min_value,
  products.max_value,
  coalesce(product_vip_price.price, products.price) as price,
  products.metadata
from
  products
  left join product_vip_price on products.id = product_vip_price.product_id
  and product_vip_price.vip_level = user_highest_vip_is ()
where
  products.is_hidden = false;

性能优化版本 #

出于性能考虑，改用视图替代函数获取用户最高VIP等级：

1. user_highest_vip_level视图 #

create or replace view user_highest_vip_level AS
SELECT max(vip.level) as vip_level FROM profiles JOIN vip ON profiles.points >= vip.points WHERE profiles.id = auth.uid()

2. 优化后的products_view视图 #

新增价格转换函数from_price_to_currency_price，并通过子查询调用视图获取VIP等级：

create view
  public.products_view as
select
  products.id,
  products.is_main,
  products.is_available,
  products.is_category,
  products.parent_id,
  products.min_value,
  products.max_value,
  from_price_to_currency_price (coalesce(product_vip_price.price, products.price)) as price,
  products.metadata
from
  products
  left join product_vip_price on products.id = product_vip_price.product_id
  and product_vip_price.vip_level = (
    (
      select
        user_highest_vip_level.vip_level
      from
        user_highest_vip_level
    )
  )
where
  products.is_hidden = false;

内容的提问来源于stack exchange，提问作者Isaac Qadri