0

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?

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.