Replace insert with rule in PostgreSQL-CodePudding

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)
);

CodePudding user response：

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

CodePudding user response：

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"');