Replace insert with rule in PostgreSQL

Question

I want to replace INSERT into a table with a PostgreSQL rule.

Here is my two tables:

Resource:
  uid (PK): UUID
  type (NOT_NULL): ENUM

SpecificResource:
  uid (PK, FK on resource.uid): UUID
  content (NOT_NULL): JSONB

I want any user to the database to be able to make insert/update/delete on SpecificResource directly without the need to insert/update/delete on Resource.

Here is my unsuccessful try that triggers an infinite recursion loop (indeed because I try to re-insert in specific_resource table with a RULE (...) DO INSTEAD :

CREATE OR REPLACE RULE insert_specific_resource
AS ON INSERT TO specific_resource
DO INSTEAD (
    INSERT INTO resource (uid, type)
    VALUES (NEW.uid, 'SPECIFIC_RESOURCE');
    
    INSERT INTO specific_resource (uid)
    VALUES (new.uid)
);

Do you insist on using a rule? Maybe an instead of trigger? — Stefanov.sm
– Stefanov.sm, Commented Nov 10, 2021 at 14:44
A trigger would be excellent also, but it's far beyond my SQL knowledge. — mlisthenewcool
– mlisthenewcool, Commented Nov 10, 2021 at 14:45
@Stefanov.sm the following trigger works well for resource table. However, it does not insert anything in specific_resource table on INSERT INTO specific_resource ... CREATE OR REPLACE FUNCTION add_specific_resource() RETURNS TRIGGER AS $BODY$ BEGIN INSERT INTO resource(uid, type) VALUES (uid, 'SPECIFIC_RESOURCE'); RETURN NEW; END $BODY$ LANGUAGE PLPGSQL; CREATE OR REPLACE TRIGGER add_dashboard BEFORE INSERT ON dashboard FOR EACH ROW EXECUTE PROCEDURE add_specific_resource(); — mlisthenewcool
– mlisthenewcool, Commented Nov 10, 2021 at 14:57

Stefanov.sm · Accepted Answer · 2021-11-10 15:29:39Z

1

create or replace function specific_resource_tf()
returns trigger language plpgsql as
$$
begin
 insert into resource(uid, type) VALUES (new.uid, 'SPECIFIC_RESOURCE');
 return new;
end;
$$;

create trigger specific_resource_t 
before insert on specific_resource
for each row
execute procedure specific_resource_tf();

Explanation

After creating trigger specific_resource_t it will be executed before each insert in table specific_resource. The trigger invokes function specific_resource_tf which does what your rule was intended to - inserts a record into resource before proceeding with the insert in table specific_resource.

Illustration (with temporary tables and function)

-- drop table if exists specific_resource; drop table if exists resource;
create temp table resource (uid integer primary key, type text);
create temp table specific_resource (uid integer references resource(uid), content JSONB);  

create or replace function pg_temp.specific_resource_tf()
returns trigger language plpgsql as $$
begin
 insert into resource(uid, type) VALUES (new.uid, 'SPECIFIC_RESOURCE');
 return new;
end;
$$;

create trigger specific_resource_t 
before insert on specific_resource
for each row execute procedure pg_temp.specific_resource_tf();

insert into specific_resource values (22, '"test"');
-- does insert in both tables

edited Nov 10, 2021 at 15:29

answered Nov 10, 2021 at 14:59

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

mlisthenewcool Over a year ago

Thanks for your answer. It does not insert any row in specific_resource when I execute INSERT INTO specific_resource (uid, content) VALUES ('an_uuid', '"test'")

Stefanov.sm Over a year ago

I do not believe that. Any error message?

mlisthenewcool Over a year ago

Seems the syntax is wrong in pgadmin : ERROR: syntax error at or near ";" for the last character. If I add parenthesis it works, but I guess it's not calling the function, trying to define it instead... But still add a row in resource and not specific_resource. Weird

Stefanov.sm Over a year ago

My omission. Missed () in the end of create trigger statement. Fixed in the answer.

mlisthenewcool Over a year ago

Sure :) Still, it inserts correctly in resource but not specific_resource..

|

mlisthenewcool · Accepted Answer · 2021-11-10 15:24:35Z

0

CREATE OR REPLACE FUNCTION add_specific_plus_resource()
RETURNS TRIGGER LANGUAGE PLPGSQL AS 
$$
BEGIN
    INSERT INTO resource(uid, type)
    VALUES (uid, 'SPECIFIC');
    RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER add_specific_resource
BEFORE INSERT ON specific_resource
FOR EACH ROW EXECUTE PROCEDURE add_specific_plus_resource();

The following INSERT works well to add a row into resource but not into specific_resource.

INSERT INTO specific_resource(uid, content)
VALUES ('123e4567-e89b-12d3-a456-426614174000', '"test"');

answered Nov 10, 2021 at 15:24

mlisthenewcool

5754 silver badges18 bronze badges

2 Comments

Stefanov.sm Over a year ago

Pls. note that OR REPLACE is illegal for create trigger

mlisthenewcool Over a year ago

@Stefanov.sm You nailed it, it works like a charm, thanks a lot !

Collectives™ on Stack Overflow

Replace insert with rule in PostgreSQL

2 Answers 2

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related