PostgreSQL多表视图插入规则中如何声明变量复用重复SELECT语句

PostgreSQL的Rule本身不支持声明自定义变量，你之前尝试的DECLARE是PL/pgSQL存储过程/触发器的专用语法，规则内部仅支持编写纯SQL语句，无法直接使用。

要解决重复查询email_id的问题，最优方案是改用链式CTE（公共表表达式）+ RETURNING子句，直接捕获每次插入生成的主键复用，完全不需要多次查表查询，修改后的规则代码如下：

create rule insert_new_user as on insert to "collHBRS".loginview do instead(
    with inserted_email as (
        -- 插入邮箱的同时返回生成的email_id，存入CTE复用
        insert into "collHBRS".email(email_addr) 
        values (new.login_name)
        returning email_id
    ),
    inserted_profile as (
        -- 直接从CTE取email_id插入profile，同时返回生成的profile_id
        insert into "collHBRS".profile(profile_email_fk, profile_address_fk, profile_student_fk, profile_company_fk)
        select email_id, null, null, null from inserted_email
        returning profile_id
    )
    -- 最后插入login表，所有主键直接从前置CTE取，无重复查询
    insert into "collHBRS".login(login_email_fk, login_password, login_salt, last_login, login_profile_fk)
    select 
        inserted_email.email_id,
        new.login_password,
        new.login_salt,
        now(),
        inserted_profile.profile_id
    from inserted_email, inserted_profile
);

如果你的逻辑后续还有扩展需求，更推荐把视图插入逻辑换成INSTEAD OF INSERT触发器，触发器函数用PL/pgSQL编写，可以自由声明变量存储中间值，可读性和可维护性比Rule更高，示例代码如下：

-- 创建触发器函数
create or replace function "collHBRS".func_insert_loginview()
returns trigger as $$
declare
    v_email_id int;
    v_profile_id int;
begin
    -- 插入邮箱并把主键存入变量
    insert into "collHBRS".email(email_addr) 
    values (new.login_name) 
    returning email_id into v_email_id;

    -- 插入profile并把主键存入变量
    insert into "collHBRS".profile(profile_email_fk, profile_address_fk, profile_student_fk, profile_company_fk)
    values (v_email_id, null, null, null)
    returning profile_id into v_profile_id;

    -- 插入login表直接用变量
    insert into "collHBRS".login(login_email_fk, login_password, login_salt, last_login, login_profile_fk)
    values (v_email_id, new.login_password, new.login_salt, now(), v_profile_id);

    return new;
end;
$$ language plpgsql;

-- 给视图绑定触发器
create trigger trg_insert_loginview
instead of insert on "collHBRS".loginview
for each row execute function "collHBRS".func_insert_loginview();

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