How to return when inserting into multiple tables as part of a views on inert rule

Question

I am trying to create rules for my views to insert into multiple tables at once but I need to return the new row and I cannot work how to return from multiple insert statements.

This rule works fine but obviously returns nothing:

create rule template_field_insert as on insert to template_field 
do instead (
    insert into public.base (
        id,
        organisation_id,
        last_modified_by,
        armicus_data
    )
    values (
        new.id,
        new.organisation_id,
        new.last_modified_by,
        new.armicus_data
    )
    insert into public.template_field_data (
        id,
        template_id,
        format_id,
        armicus_name,
        description,
        armicus_configuration,
        required
    )
    values (
        new.id,
        new.template_id,
        new.format_id,
        new.armicus_name,
        new.description,
        new.armicus_configuration,
        new.required
    )
) 

I tried this but it just throws a syntax error on the first insert:

create rule template_field_insert as on insert to template_field 
do instead (
    (
        insert into public.base (
            id,
            organisation_id,
            last_modified_by,
            armicus_data
        )
        values (
            new.id,
            new.organisation_id,
            new.last_modified_by,
            new.armicus_data
        )
        returning *
    ) b, (
        insert into public.template_field_data (
            id,
            template_id,
            format_id,
            armicus_name,
            description,
            armicus_configuration,
            required
        )
        values (
            new.id,
            new.template_id,
            new.format_id,
            new.armicus_name,
            new.description,
            new.armicus_configuration,
            new.required
        )
        returning *
    ) f
    select b.id, f.armicus_name
    from b
    join f on b.id = f.id
)

I have achieved this with CTEs outside of the view but you cannot use NEW inside a CTE so cannot use this approach inside the rule. I also tried using subqueries but I couldn't work out how to structure it because I wasn't sure what to enter in for the FROM clause.

How do I convert this query to be returning?

Answer 1

Model.

Insert data into two tables via view with returning some field values.

Test tables:

create table test1 (id int, val int);
create table test2 (id int, val int);

View:

create view total 
as
select t1.id, t1.val val1, t2.val val2
from test1 t1
join test2 t2 on t1.id = t2.id;

Trigger function and trigger which inserts data into tables:

create function fninsert()
returns trigger as $fninsert$
begin
insert into test1 values (new.id, new.val1);
insert into test2 values (new.id, new.val2);
return new;
end;
$fninsert$ LANGUAGE plpgsql;

create trigger trinsert
instead of insert on total
for each row
execute procedure fninsert();

Rule which returns inserted values:

create rule r_insert as 
on insert to total
do also
select new.val1, new.val2;

Demo fiddle.